CODEWITHSUNDEEP
sqljsonsql-servert-sql
Denys
Denys

Reputation: 53

Using JSON_VALUE for parse column in SQL Server table

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

Upvotes: 1

Views: 478

Answers (2)

Anduamlak Tadesse
Anduamlak Tadesse

Reputation: 1

select I.id
from openjson(@json)
with(
tags nvarchar(max) '$.tags' as JSON ) M
CROSS APPLY OPENJSON(M.tags)
WITH (
ID Varchar(20) '$.id'
) as I;

Upvotes: -1

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Related Questions