How to extract values from nested json objects using JSON Extractor about usql HOT 9 CLOSED

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.

from usql.

Hi. Any updates regarding this bug?

from usql.

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

from usql.

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.

from usql.

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

from usql.

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 :)

from usql.

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

from usql.

Excellent thanks Chad.

from usql.

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

from usql.