我在 SQL Server 中有这样的数据：
| 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"}] |
我需要解析这些数据来得到这样的东西：
| ID | 值1 | 值2 | 值3 | 值4 |
|---|---|---|---|---|
| 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"} | 空值 | 空值 |
你可以尝试使用：
SELECT value
FROM dbo.YourTableNameHere
CROSS APPLY
OPENJSON(Json_Data, '$')
要获得基于行的输出 - 或者如果您可以确定您永远不会有超过 4 个条目，您也可以使用这个：
SELECT
JSON_QUERY(Json_Data, '$[0]'),
JSON_QUERY(Json_Data, '$[1]'),
JSON_QUERY(Json_Data, '$[2]'),
JSON_QUERY(Json_Data, '$[3]')
FROM
dbo.YourTableNameHere
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句