Search code examples
pythonjsonsql-serverjson-query

Extract the Data from Json to Table where Id is in Key

I am trying to covert the below Json to table using SQL Function JSON_QUERY ( expression [ , path ] ). When i use this i am unable to define the path as Id is in the key and id's change overtime. Is there any way we can convert/load this json format to a table?

Solution

  • You can achieve it using the OPENJSON table valued function:

    SELECT  ch.[key] as checklistId, 
        JSON_VALUE (ch.[value] ,'$.isChecked') as isChecked,
        JSON_VALUE (ch.[value],'$.orderHint') as orderHint

FROM OPENJSON (@json,'$.checklist') ch;