One of my tables contains JSON values in each row of a column.
The data is as below (example. one row)
[{"id":"30a66bec-c0aa-4655-a8ef-506e52bfcc14","type":"nps","value":"promoter","decimalValue":"10"},{"id":"37850b3b-1eac-4921-ae22-b2f6d2450897","type":"sentiment","value":"positive","decimalValue":"0.990000009536743"}]
Now I’m trying to retrieve two columns from it. (id, value)
I’m writing the below query using JSON_VALUE but getting NULL values in each row of the new column.
select a.jsondata,JSON_VALUE(a.jsondata,'$.id') from table as a
>Solution :
Your JSON field is an array so you need to specify which element you’re after, assuming its always the first you can use:
select a.jsondata,JSON_VALUE(a.jsondata,'$[0].id') from table as a
You need to change the index inside the square brackets to access the id you want from the JSON string