时间戳

首页 > 解决方案 > 为什么我无法使用 SQL 访问 JSON 的子节点？

json - 为什么我无法使用 SQL 访问 JSON 的子节点？

问题描述

我对这个有点生气，而且我敢肯定，最后一个小时没有盯着它的人很容易发现它……

我有以下 JSON（有更多“详细信息”示例，但对于这个问题，两个就足够了：

[
    {
        "MonthDetails": [
            {
                "Month": 1,
                "MonthName": "January 2020",
                "Details": [
                    {
                        "ServiceUserName": "Resident 1",
                        "ServiceUserLastName": "Resident",
                        "SUID": "b5259252-2018-43ee-a7a9-842fa7c80473",
                        "InfectionType": "Urinary tract",
                        "DateStarted": "2019-11-05T13:53:01.9392086+00:00",
                        "DateCleared": "Open",
                        "NextReviewDate": "2019-11-06T13:53:01.9392086+00:00",
                        "AntibioticsStartDate": "2019-11-05T00:00:00+00:00",
                        "AntibioticsEndDate": "2019-11-07T00:00:00+00:00",
                        "AcquiredOnAdmission": false,
                        "NeedsBarrierNursing": false,
                        "IsOpen": true,
                        "DaysToClear": 0,
                        "SURecordOpen": true
                    },
                    {
                        "ServiceUserName": "Resident 2",
                        "ServiceUserLastName": "Resident",
                        "SUID": "376cffba-52ce-4de0-855d-dd9e24f466e0",
                        "InfectionType": "Chest",
                        "DateStarted": "2019-12-04T12:11:30.7777465+00:00",
                        "DateCleared": "Open",
                        "NextReviewDate": "2019-12-05T12:11:30.7777465+00:00",
                        "AntibioticsStartDate": "2019-12-01T00:00:00+00:00",
                        "AntibioticsEndDate": "2019-12-08T00:00:00+00:00",
                        "AcquiredOnAdmission": true,
                        "NeedsBarrierNursing": false,
                        "IsOpen": true,
                        "DaysToClear": 0,
                        "SURecordOpen": true
                    }
                ],
                "criteria": null,
                "Errors": {},
                "DateFrom": "0001-01-01T00:00:00",
                "SelectedDateRange": null,
                "NumberOfDays": 0
            }
        ]
    }
]

我的理解是，如果我想获得例如。MonthDetails.Details >> ServiceUserName、SUID 等，我可以使用以下代码：

SELECT * INTO Residents FROM OPENJSON(@JSON, '$.MonthDetails.Details') 
    WITH
    (   
        ServiceUserName nvarchar(100) '$.ServiceUserName',
        ServiceUserLastName nvarchar(100) '$.ServiceUserName',
        SUID nvarchar(150) '$.SUID',
        InfectionType nvarchar(30) '$.InfectionType',
        DateStarted nvarchar(10) '$.DateStarted',
        DateCleared nvarchar(10) '$.DateCleared',
        NextReviewDate nvarchar(10) '$.NextReviewDate',
        AntibioticsStartDate nvarchar(10) '$.AntibioticsStartDate',
        AntibioticsEndDate nvarchar(10) '$.AntibioticsEndDate',
        AcquiredOnAdmission nvarchar(5) '$.AcquiredOnAdmission',
        NeedsBarrierNursing nvarchar(5) '$.NeedsBarrierNursing',
        IsOpen nvarchar(5) '$.IsOpen',
        DaysToClear nvarchar(5) '$.DaysToClear',
        SURecordOpen nvarchar(5) '$.SURecordOpen'
    )

但是，所发生的只是一条记录在每一列中创建一个带有 NULL 的条目。我知道我可以只做 $.MonthDetails 然后将详细信息作为 JSON 传递并交叉应用它，但我认为上面的首选方法也应该有效？

谢谢蚂蚁

标签： jsonsql-server

解决方案

用于数组中的单个对象和多个详细信息

select *
from openjson(@json, '$[0].MonthDetails[0].Details')
with
(
    ServiceUserName varchar(50),
    ServiceUserLastName varchar(50),
    SUID uniqueidentifier,
    InfectionType varchar(50),
    DateStarted datetimeoffset(7),
    DaysToClear int
);

对于数组中的任意数量的对象（调整其余元素/对象）

select *
from openjson(@json) AS t
cross apply openjson(t.value)
with 
(
    MonthDetails nvarchar(max) as json
) AS md
cross apply openjson(md.MonthDetails)
with
(
    [Month] tinyint,
    MonthName varchar(20),
    criteria varchar(50),
    DateFrom datetime2(0),
    SelectedDateRange varchar(10),
    NumberOfDays int,
    Details nvarchar(max) as json
) as mdd
cross apply openjson (mdd.Details)
with
(
    ServiceUserName varchar(50),
    ServiceUserLastName varchar(50),
    SUID uniqueidentifier,
    InfectionType varchar(50),
    DateStarted datetimeoffset(7),
    DaysToClear int
    --,...............
) as det;

推荐阅读