时间戳

首页 > 解决方案 > 带有数组的 OPENJSON

sql-server - 带有数组的 OPENJSON

问题描述

我有一个大型 JSON 结构（数组数组），由标识每个部分的字符串值（例如“1”）组成。

我将如何利用 OPENJSON 正确解析所有信息？

JSON 数据的子集：

{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}

我尝试了以下多种变体，但均未成功：

SELECT @json1 = BulkColumn
 FROM OPENROWSET (BULK 'C:\4\test3.json', SINGLE_CLOB) as j

 SELECT product, category, item FROM OPENJSON (@json1, '$.products[0]')
  With (
    product varchar(20),
    category varchar(20),
    item varchar(20)
  )

有谁知道我做错了什么？

标签： sql-server

解决方案

您没有提供预期的结果，但您可以尝试以下方式：

DECLARE @json NVARCHAR(MAX)=
N'{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}';

SELECT B.*
FROM OPENJSON(@json,N'$.products')
WITH([1] NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.[1])
WITH(product NVARCHAR(100)
    ,category INT
    ,item INT
    ,[location] INT
    ,quantity INT) B;

在有多个部分的情况下，您需要将“1”作为结果集中的一列，您可以尝试以下操作：

DECLARE @json NVARCHAR(MAX)=
N'{"products":[
    {"1":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
    ,{"2":[
        {"product":"01-223","category":"32","item":"16326","location":"06","quantity":"71"},
        {"product":"01-223","category":"32","item":"16327","location":"06","quantity":"44"},
        {"product":"01-223","category":"32","item":"16328","location":"06","quantity":"47"}
        ]
    }
]}';

SELECT B.[key] AS NumericObjectName
      ,C.*
FROM OPENJSON(@json,N'$.products') A
CROSS APPLY OPENJSON(A.[value]) B
CROSS APPLY OPENJSON(B.[value])
WITH(product NVARCHAR(100)
    ,category INT
    ,item INT
    ,[location] INT
    ,quantity INT) C

推荐阅读