SQL中的JSON解析

Question

我正在尝试以表格形式解析sql server中的JSON数组，下面是数据字符串

CREATE TABLE #JSONData
(
[gca] NVARCHAR(MAX)
,[User] INT
);
INSERT INTO #JSONData
VALUES ('
{"items":[{"i":"image1",
                 "url":"url-1",
                 "name":"Product 1",
                 "quantity":"1",
                 "sku":"112233",
                 "subtotal":"$24.99"},
                 {"i":image3",
                 "url":"url-3",
                 "name":"Product 3",
                 "quantity":"1",
                 "sku":"112255",
                 "subtotal":"$7.99"}]}
        ', 1)

我正在使用Microsoft SQL Server 2016

我正在尝试使用IS JSON函数来检查它是否是有效的JSON字符串-肯定的，但是当使用JSON _VALUE函数时，我得到null输出

SELECT [User],
       [gca], 
       ISJSON([gca]) AS [ISJSON], 
       JSON_VALUE([gca], '$.i') AS [i],
       JSON_VALUE([gca], '$.name') AS [Product],
       JSON_VALUE([gca], '$.quantity') AS [Quantity],
       JSON_VALUE ([gca], '$.subtotal') AS [subtotal],
       [User]

FROM #JSONData

​

​

Answer 1

以下方法可能会帮助您获得预期的结果。函数JSON_VALUE从JSON字符串中提取标量值。输入JSON的$.items部分是一个JSON数组，因此您需要使用具有显式模式的OPENSJSON()将此JSON数组解析为表：

表：

CREATE TABLE #JSONData ( 
   [gca] NVARCHAR(MAX),
   [User] INT
); 
INSERT INTO #JSONData 
VALUES 
   (N'
{"items":[{"i":"image1",
                 "url":"url-1",
                 "name":"Product 1",
                 "quantity":"1",
                 "sku":"112233",
                 "subtotal":"$24.99"},
                 {"i":"image3",
                 "url":"url-3",
                 "name":"Product 3",
                 "quantity":"1",
                 "sku":"112255",
                 "subtotal":"$7.99"}]}
        ', 1)

声明：

SELECT 
   j.*,
   d.[User] AS u
FROM #JSONData d
CROSS APPLY OPENJSON(d.[gca], '$.items') WITH (
   i varchar(100) '$.i',
   p varchar(50) '$.name',
   q int '$.quantity',
   s varchar(20) '$.subtotal'
) j

输出：

---------------------------------
i       p           q   s       u
---------------------------------
image1  Product 1   1   $24.99  1
image3  Product 3   1   $7.99   1