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

CodePudding user response：

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