CODEWITHSUNDEEP
sqlarraysjsonsql-servert-sql
navid karampour
navid karampour

Reputation: 93

Json Value in SQL Server when we have a list

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

Upvotes: 0

Views: 54

Answers (1)

marc_s
marc_s

Reputation: 754408

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

Upvotes: 1

Related Questions