Using JSON_VALUE for parse column in SQL Server table

Question

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

Zhorov · Accepted Answer · 2022-03-18 14:27:39Z

2

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

edited Mar 18, 2022 at 14:27

answered Mar 18, 2022 at 14:15

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thom A Over a year ago

Added note, if the column you define within the WITH clause has the same name as the entity in the JSON, you don't need to define the entity. So WITH (id varchar(10)) would be sufficient here.

Zhorov Over a year ago

@Larnu, I agree, I just usually use the full syntax. And if I remember correctly, in this case the match is case sensitive.

Thom A Over a year ago

It is case sensitive, yes.

Denys Over a year ago

Thanks it is work:) But if this json in table columns, I`m trying SELECT id FROM OPENJSON((select ENTITY from dbo.test), '$.tags') WITH (id varchar(10) '$.id') but got error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. When try select EnData.[id] from dbo.test q CROSS APPLY OPENJSON (q.tags) as EnData got error too

Denys Over a year ago

@Zhorov select *, tt.id, tt.date from dbo.Test q CROSS APPLY OPENJSON ( ENTITY, 'lax $.tags') WITH (id varchar(max) '$.id') as tt it is help me) Thank you

Mark Rotteveel · Accepted Answer · 2023-11-01 09:21:18Z

-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;

edited Nov 1, 2023 at 9:21

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Oct 31, 2023 at 12:46

Anduamlak Tadesse

11 bronze badge

1 Comment

Jeremy Caney Over a year ago

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach?

Collectives™ on Stack Overflow

Using JSON_VALUE for parse column in SQL Server table

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related