Json Value in SQL Server when we have a list

Question

I have a data in SQL Server like this:

ID	Json_Data
1	[{"Book_id":"6842","index":1,"type":"BOOK"},{"Book_id":"103735","index":2,"type":"BOOK"}, {"Book_id":"104253","index":3,"type":"BOOK_GIFT"}, {"Book_id":"83886","index":4,"type":"BOOK"}]
2	[{"Book_id":"688","index":1,"type":"BOOK"},{"Book_id":"548","index":2,"type":"BOOK"}]

I need to parse this data to get something like this :

id	Value1	Value2	Value3	Value4
1	{"Book_id":"6842","index":1,"type":"BOOK"}	{"Book_id":"103735","index":2,"type":"BOOK"}	{"Book_id":"104253","index":3,"type":"BOOK_GIFT"}	{"Book_id":"83886","index":4,"type":"BOOK"}
2	{"Book_id":"688","index":1,"type":"BOOK"}	{"Book_id":"548","index":2,"type":"BOOK"}	Null	Null

marc_s · Accepted Answer · 2021-09-19 11:40:40Z

1

You could try to use:

SELECT value
FROM dbo.YourTableNameHere
CROSS APPLY
    OPENJSON(Json_Data, '$')

to get a row-based output - or if you can be sure you never have more than 4 entries, you could also use this:

SELECT
    JSON_QUERY(Json_Data, '$[0]'),
    JSON_QUERY(Json_Data, '$[1]'),
    JSON_QUERY(Json_Data, '$[2]'),
    JSON_QUERY(Json_Data, '$[3]')
FROM
    dbo.YourTableNameHere

answered Sep 19, 2021 at 11:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Json Value in SQL Server when we have a list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related