Using JSON_VALUE for parse column in SQL Server table

ncgqoxb0 于 2023-11-16 发布在 SQL Server

关注(0)|答案(2)|浏览(133)

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:

sql-server

来源：https://stackoverflow.com/questions/71528354/using-json-value-for-parse-column-in-sql-server-table

2条答案

按热度按时间

hlswsv351#

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

赞(0）回复(0）举报 2023-11-16

j1dl9f462#

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;

赞(0）回复(0）举报 2023-11-16

我来回答

Using JSON_VALUE for parse column in SQL Server table

2条答案

相关问题

热门标签

最新问答