时间戳

首页 > 解决方案 > 如何将嵌套的 JSON 导入 SQL Server 表？

sql - 如何将嵌套的 JSON 导入 SQL Server 表？

问题描述

我有一个嵌套的 JSON 变量（我在这里使用缩短的版本），我想将它插入到表中。我可以不使用嵌套的列和值，但可以使用嵌套部分。我想要的是检索 2 行：一个用于 sessionID = 20，另一个用于 30 的子列。我正在使用 SQL Server 2017。

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'{
"List":
[
    {
    "ID": 13,
    "Date": "2015-12-07",
    "SessionID": {
      "20": {
        "discount": "no",
        "price": 15.99
            },
      "30": {
       "discount": "yes",
        "price": 12.99
            }
        }
    }
]
}'  



SELECT *  
FROM OPENJSON(@json, N'lax $.List')
WITH (ID int '$.ID' 
     ,[Date] date '$.Date'
     ,SessionID nvarchar(max) N'lax $.SessionID' AS JSON
     )

我想检索如下数据。提前感谢您的帮助！

我想检索这样的查询

标签： sqljsonsql-servertsql

解决方案

正如 Zohard Peled 告诉您的，JSON 的内部结构需要改进。一般来说，将数据作为元素名称放置并不是一个好主意。最好使用类似的东西"SessionID":"20"，而您的 JSON 会提供"20"以下对象的 as 名称。

尽管如此，这可以使用以下列来key完成OPENJSON：

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'{
"List":
[
    {
    "ID": 13,
    "Date": "2015-12-07",
    "SessionID": {
      "20": {
        "discount": "no",
        "price": 15.99
            },
      "30": {
       "discount": "yes",
        "price": 12.99
            }
        }
    }
]
}'

--查询将逐步遍历您的结构：

SELECT B.ID
      ,B.[Date]

      ,C.[key] AS SessionID --here's the magic...

      ,JSON_VALUE(C.[value],'$.discount') AS discount
      ,JSON_VALUE(C.[value],'$.price') AS price
FROM OPENJSON(@json)
WITH(List NVARCHAR(MAX) AS JSON) A --getting "List" as JSON
CROSS APPLY OPENJSON(A.List)
WITH(ID INT
    ,[Date] DATE
    ,SessionID NVARCHAR(MAX) AS JSON) B --getting "ID" and "Date" and "SessionID as JSON
CROSS APPLY OPENJSON(B.SessionID) C; --get the objects within the array, but nameless...

结果：

ID  Date    SessionID   discount    price
13  2015-12-07  20      no          15.99
13  2015-12-07  30      yes         12.99

推荐阅读