使用嵌套数组查询JSON列

q5iwbnjs 于 2023-02-26 发布在其他

关注(0)|答案(2)|浏览(208)

下面是我到目前为止构建的JSON和查询，我觉得我只是错过了一些愚蠢的片段。这些数据文件被原样加载到SQL Server表中，所以这个JSON存在于一列中。
最终嵌套数组返回空值，其后续字段返回空值。

希望了解如何更改查询以将“plans”作为其数组，以及如何从中提取各个字段

JSON示例

{"carrierPlans": [{"carrierId": 90, "carrierName": "Community Care", "plans": [{"planId": 170, "planName": "Silver", "pbpId": "H167"}]}]}

质询

-- carrierplans
SELECT  
c.[carrierId],
c.[carrierName],
c.[pbpId],
c.[planName],
c.[planId]
FROM [MyDatabase].[dbo].[LoadTable] b
    OUTER APPLY OPENJSON (b.[array]) -- array is the json column
WITH (
[carrierId] varchar(50),
[carrierName] varchar(50),
[pbpId] varchar(50),
[planName] varchar(50),
[planId] varchar(50)
) c
WHERE [key] = 'carrierplans'

或者如果我想捕获像这样的plans数组

SELECT  
c.[carrierId],
c.[carrierName],
c.[plans]
FROM [MyDatabase].[dbo].[LoadTable] b
    OUTER APPLY OPENJSON (b.[array]) -- array is the json column
WITH (
[carrierId] varchar(50),
[carrierName] varchar(50),
[plans] varchar(50)
) c
WHERE [key] = 'carrierplans'

当前结果

carrierId   carrierName pbpId   planName    planId
90          Community Care  NULL    NULL            NULL

JSON

来源：https://stackoverflow.com/questions/75527310/querying-json-column-with-nested-arrays

2条答案

按热度按时间

bwitn5fc1#

请尝试以下解决方案。

- SQL语言**

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, j NVARCHAR(MAX));
INSERT @tbl (j) VALUES
(N'{
    "carrierPlans": [
        {
            "carrierId": 90,
            "carrierName": "Community Care",
            "plans": [
                {
                    "planId": 170,
                    "planName": "Silver",
                    "pbpId": "H167"
                }
            ]
        }
    ]
}');
-- DDL and sample data population, end

-- Method #1
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , c.*
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a
    CROSS APPLY OPENJSON (A.value, '$.plans') 
WITH (
    [planId] INT'$.planId',
    [carrierName] varchar(50) '$.planName',
    [pbpId] varchar(50) '$.pbpId'
) as c;

-- Method #2
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , plans = b.value
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a
    CROSS APPLY OPENJSON (A.Value, '$.plans') AS b;

-- Method #3
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , plans = JSON_QUERY(a.Value,'$.plans')
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a;

- 输出#1**

| 运营商ID|运营商名称|计划ID|运营商名称|pbpId|
| - ------|- ------|- ------|- ------|- ------|
| 九十|社区护理|一百七十|银|H167|
输出#2
| 运营商ID|运营商名称|计画|
| - ------|- ------|- ------|
| 九十|社区护理|{"计划ID "：170，"计划名称"："银色"、"pbpId"："H167"|

赞(0）回复(0）举报 2023-02-26

eyh26e7m2#

我发现这是不可能做到的，SQL Server中的JSON支持很好，但有限。
我改为使用XML，他们有功能非常全面的选择性查询，这让我可以在嵌套块内查询，并对XML文档的部分内容进行索引。
您将得到如下查询：

SELECT xnodes.vals.value('.', 'int') AS [Carrier] FROM [LoadTable]
  CROSS APPLY MyXMLColumn.nodes('/carrierPlans') AS xnodes(vals) 
WHERE xnodes.vals.value('./@carrierID', 'int') = '90'

this link对于概念非常有用，而this one对于创建索引-选择性索引是强大之处，它真的很好。
缺点是您必须创建XML文档而不是JSON，并且MS可能会向JSON查询添加与XML中相同的选择性索引功能。

赞(0）回复(0）举报 2023-02-26

我来回答

使用嵌套数组查询JSON列

2条答案

相关问题

热门标签

最新问答