How to extract values from nested json objects using JSON Extractor

[sarath-rachuri]

I have a json file that contains a few properties, say,

{
  "A": [
    {
      "property1": "value 1",
    },
    {
      "A1": [
        {
          "property2": "value 2",
          "property3": "value 3"
        }
      ],
      "property4": "value 4"
    }
  ],
  "B": {
     "custom": {
      "B1": [
        {
           "property5": "value 5",
           "property6": "value 6",
           "property7": "value 7"
        }        
      ],
      "metrics": [ ]
    },
    "property8": { }
  }
}

Few of them are nested. How to extract the nested property values using JSON extractor. For example, I want to extract the values of properties 5,6,7.

[nibras85]

Hi. Any updates regarding this bug?

[rukmanig]

Hi, you can use the JsonTuple function to get the values, the code snippet to extract the properties 5,6 and 7 is done as follows

@jsonnodes =
SELECT JsonApp.JsonFunctions.JsonTuple(jsonstring, "$..property5", "$..property6", "$..property7") AS json_map
FROM @JSON;

@nodes_exploded = SELECT name, node_value
FROM @jsonnodes
CROSS APPLY
EXPLODE(json_map) AS T(name, node_value);

The snippet above will give you an output as follows
"custom.B1[0].property5", "value 5"
"custom.B1[0].property6" , "value 6"
"custom.B1[0].property7", "value 7"

Please let me know if this helps and if you have any further questions.

[chadequate]

Thanks for the reply! What does @b1_property refer to here?

[rukmanig]

Sorry Chad, updated to fix typo in code to call the right variable.

[chadequate]

I don't quite understand what jsonstring refers to here. Isn't the input coming from @JSON?

Sorry for the sporadic questioning. I keep getting pulled onto other things :)

[rukmanig]

jsonstring is the string you extracted out of the JSON using the JSONExtractor. I have also published code that parses Json on the github site - please do take a look at https://github.com/Azure/usql/blob/master/Examples/JsonSample/JsonSample/NestedJsonParsing.usql that will give the full script that starts with extracting content from a JSON file and manipulating the data. Please let me know if you have any questions.

[chadequate]

I'm able to extract nested objects now. Thanks for all your help!

[rukmanig]

Excellent thanks Chad.

[GauravSagne]

Hi rukmanig,

I've a nested json for which I've used JsonFunction.JsonTuple() in conjuction with EXPLODE() but that nested json have a sub-properties too. Could you please tell me how to read the sub-properties using JsonFunction.JsonTuple (), please ?

Thanks