JSON To Table Pivoting

Hello,

I have a JSON source that is producing an output similar to the below:

{
  "skinType" : {
    "Label" : "Skin Type",
    "Values" : [ {
      "Value" : "normal",
      "Count" : 116
    }, {
      "Value" : "combination",
      "Count" : 405
    }, {
      "Value" : "dry",
      "Count" : 215
    }, {
      "Value" : "oily",
      "Count" : 54
    } ],
    "Id" : "skinType"
  },
  "eyeColor" : {
    "Label" : "Eye Color",
    "Values" : [ {
      "Value" : "blue",
      "Count" : 143
    }, {
      "Value" : "brown",
      "Count" : 411
    }, {
      "Value" : "green",
      "Count" : 106
    }, {
      "Value" : "hazel",
      "Count" : 114
    }, {
      "Value" : "gray",
      "Count" : 5
    } ],
    "Id" : "eyeColor"
  },
  "skinConcerns" : {
    "Label" : "Skin concerns",
    "Values" : [ {
      "Value" : "acne",
      "Count" : 313
    }, {
      "Value" : "aging",
      "Count" : 173
    }, {
      "Value" : "blackheads",
      "Count" : 69
    }, {
      "Value" : "cellulite",
      "Count" : 6
    }, {
      "Value" : "cuticles",
      "Count" : 2
    }, {
      "Value" : "darkCircles",
      "Count" : 65
    }, {
      "Value" : "dullness",
      "Count" : 45
    }, {
      "Value" : "pores",
      "Count" : 7
    }, {
      "Value" : "puffiness",
      "Count" : 2
    }, {
      "Value" : "redness",
      "Count" : 27
    }, {
      "Value" : "sensitivity",
      "Count" : 22
    }, {
      "Value" : "sunDamage",
      "Count" : 12
    }, {
      "Value" : "unevenSkinTones",
      "Count" : 4
    } ],
    "Id" : "skinConcerns"
  },
  "StaffContext" : {
    "Label" : "I am a Sephora employee",
    "Values" : [ {
      "Value" : "true",
      "Count" : 10
    }, {
      "Value" : "false",
      "Count" : 784
    } ],
    "Id" : "StaffContext"
  },
  "hairColor" : {
    "Label" : "Hair color",
    "Values" : [ {
      "Value" : "blonde",
      "Count" : 201
    }, {
      "Value" : "brunette",
      "Count" : 373
    }, {
      "Value" : "auburn",
      "Count" : 16
    }, {
      "Value" : "black",
      "Count" : 161
    }, {
      "Value" : "red",
      "Count" : 24
    }, {
      "Value" : "gray",
      "Count" : 10
    } ],
    "Id" : "hairColor"
  },
  "skinTone" : {
    "Label" : "Skin Tone",
    "Values" : [ {
      "Value" : "fair",
      "Count" : 139
    }, {
      "Value" : "light",
      "Count" : 213
    }, {
      "Value" : "medium",
      "Count" : 231
    }, {
      "Value" : "olive",
      "Count" : 54
    }, {
      "Value" : "deep",
      "Count" : 24
    }, {
      "Value" : "dark",
      "Count" : 21
    }, {
      "Value" : "porcelain",
      "Count" : 44
    }, {
      "Value" : "tan",
      "Count" : 61
    }, {
      "Value" : "ebony",
      "Count" : 5
    } ],
    "Id" : "skinTone"
  },
  "IncentivizedReview" : {
    "Label" : "I received this product as a free sample",
    "Values" : [ {
      "Value" : "true",
      "Count" : 376
    }, {
      "Value" : "false",
      "Count" : 418
    } ],
    "Id" : "IncentivizedReview"
  },
  "age" : {
    "Label" : "Age",
    "Values" : [ {
      "Value" : "13to17",
      "Count" : 4
    }, {
      "Value" : "18to24",
      "Count" : 13
    }, {
      "Value" : "25to34",
      "Count" : 17
    }, {
      "Value" : "35to44",
      "Count" : 12
    }, {
      "Value" : "45to54",
      "Count" : 8
    }, {
      "Value" : "over54",
      "Count" : 8
    } ],
    "Id" : "age"
  }
}

I really never know what it will be. Its in a table with 1000s of records and each row has a separate one. I am trying to get it to:
RowID - Other Data - SkinType.Normal - SkinType.Combination …
0 - Bla Bla - 116 - 405 etc…

basically taking the json Label and value in the heading and the count as the value in the row.

Any ideas?

Hi @nxfxcom,

Can you give more details about your desired output?

What is “Other Data”?

From your description it seems as if you want single table with a single row. Is that actually the case? Or is it that you want a separate table for each ID (i.e. a Skin Type table, an Eye Color table, a Skin concerns table, etc.)?

1 Like

I assume you can try json path node to extract the data

1 Like

Hi @nxfxcom,
I made an example workflow that creates the headers automatically from the combination of the “id” and “value” strings. It uses a combination of JSON path to get the necessary information, some data processing nodes to get everything in the correct format, and a JSON Path (Dictionary) node to execute the created queries. This should hopefully work with new data that has different value names, but the same overall structure.
Please let me know if that solves the problem!
JSON To Table Pivoting.knwf (18.4 KB)

@Daniel_Weikert I didn’t see a possibility to do this with JSON Path alone, as the value cannot be used as a column header here and there are too many single queries to generate them all manually. Still, if somebody finds a more elegant solution, I would be curious to see it, as it might simplify my own workflows.

3 Likes