Using JSON_VALUE for parse column in SQL Server table-CodePudding

I have never worked with JSON in SQL Server before that's why need some help.

I have written a simple snippet of code:

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
  
   "id":"40476",
   "tags":[
      {
         "id":"5f5883",          
      },
      {
         "id":"5fc8",
 }
   ],
   "type":"student",
   "external_id":"40614476"
}'


SELECT
    JSON_value(@json, '$.tags[0].id') as tags

In sample above I write code how get first "id" from "tags".

But how looks like script if in "tags" not 2 "id", but an unknown number this "id" and result should be in column like this:

 1    5f5883  
 2    5fc8

CodePudding user response：

You may use OPENJSON() with explicit schema to parse the $.tags JSON array:

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
   "id":"40476",
   "tags":[
      {
         "id":"5f5883"          
      },
      {
         "id":"5fc8"
      }
   ],
   "type":"student",
   "external_id":"40614476"
}'

SELECT id
FROM OPENJSON(@json, '$.tags') WITH (id varchar(10) '$.id')

Result:

id
------
5f5883
5fc8

If you want to get the index of each id in the $.tags JSON array, then you need a combination of OPENJSON() with default schema and JSON_VALUE():

SELECT CONVERT(int, [key]) AS rn, JSON_VALUE([value], '$.id') AS id
FROM OPENJSON(@json, '$.tags')

Result:

rn  id
----------
0   5f5883
1   5fc8