Parsing Embedded JSON

[ohsocreamy]

Hi All.

Hit a bit of a snag with this one... Was wondering if anyone could point me in the right direction?

I have two JSON datasets:

Set #1:

[
    {
        "id": "ABC1",
        "name": "ABC Corp"
    },
    {
        "id": "DEF2",
        "name": "XYZ Corp Pte Ltd."
    },
    {
        "id": "XYZ3",
        "name": "NSK Kabushiki Gaisha"
    }
]

Set #2:

[
    {
        "id": "DEF2",
        "value": "{\"fields\":{\"Criteria1\":\"50\",\"Criteria2\":\"10\",\"Criteria3\":\"John Howard\"}}"
    }
]

Basically, I need to take the "fields" out of "Value" in set #2, and break them out into their own table.  Then merge this table with the relevant row of a table created from Set #1.

The result should be something like:

id	name	Criteria1	Criteria2	Criteria3
ABC1	ABC Corp
DEF2	XYZ Corp Pte Ltd.	50	10	John Howard
XYZ3	NSK Kabushiki Gaisha

I appreciate that this is quite complex to handle client side - but unfortunately I have no option.

I'm comfortable merging the datasets once I have the second one cleaned up...  Unfortunately, as a novice with this language, everything I have tried to get to "clean" it has failed.

Thanks for your help!!

[maxotek]

Here's a different take on the solution using Tool Slick.

1. Take Set #2 and paste it in the input of JSON Formatter.
2. Select Format Embedded JSON in settings.
3. Click on Format
4. Take Set #1 and paste it in the input of JSON to CSV Converter
5. Copy the subset of output of Set #2:-

   "Criteria1": "50",
   "Criteria2": "10",
   "Criteria3": "John Howard"

6. Paste it in the JSON to CSV Converter inside the object with id "DEF2", below the name property. So that the input becomes:-

   [
       {
           "id": "ABC1",
           "name": "ABC Corp"
       },
       {
           "id": "DEF2",
           "name": "XYZ Corp Pte Ltd.",
           "Criteria1": "50",
           "Criteria2": "10",
           "Criteria3": "John Howard"
       },
       {
           "id": "XYZ3",
           "name": "NSK Kabushiki Gaisha"
       }
   ]

7. Hit Convert

You will get the same output:-

id,name,Criteria1,Criteria2,Criteria3
ABC1,ABC Corp,,,
DEF2,XYZ Corp Pte Ltd.,50,10,John Howard
XYZ3,NSK Kabushiki Gaisha,,,

ABC1	ABC Corp
DEF2	XYZ Corp Pte Ltd.	50	10	John Howard
XYZ3	NSK Kabushiki Gaisha

[MarcelBeug · Accepted solution]

This is pretty straightforward: copy the JSON into a new query in Power BI, press some buttons until it is a table, repeat for the second JSON and finally merge the 2 tables,

This 3 minute video shows it all. So just go ahead and start clicking.

[ohsocreamy]

MarcelBeug thank-you so much!  This made my day.  

Worked perfectly. 🙂