使用cross apply & OPENJSON读取嵌套的JSON平面文件

biswetbf 于 2023-10-21 发布在其他

关注(0)|答案(1)|浏览(134)

我有以下JSON数据，我试图使用openjson和交叉应用来检索数据。

[
  [
    {
        "Staff": {
            "StaffKey": "abcdefg-6136-478f-abcdefg-09db9d374b0a",
            "Id": "123456",
            "FirstName": "John",
            "LastName": "Doe",
            "Email": "",
            "Tags": null
        },
        "Location": {
            "CompanyKey": "abcdefg-f99d-465a-abcdefg-fc8511058bbf",
            "LocationKey": 987654,
            "Id": "99",
            "Name": "Acme, Inc.",
            "Address": "123 Main St  CityUSA  NY 12345",
            "Abbrev": "Acme, Inc.",
            "Notes": null,
            "TimeZone": null,
            "Tags": null
        },
        "IsCredentialed": false,
        "InactiveDate": null,
        "Credentials": [],
        "Tags": null,
        "ExtendedCredentialingFields": {
            "LocationIds": [
                "65"
            ],
            "CompanyKey": "abcdefg-32b8-42cf-abcdefg-5d5e96fbc43c",
            "LocationDetails": {
                "Name": "Acme, Inc.",
                "CorporateEntity": "ACME",
                "AddressLine1": "123 Main St",
                "AddressLine2": null,
                "City": "CityUSA",
                "State": "NY",
                "Zip": "12345",
                "PhoneNumber": null,
                "Ext": null,
                "Fax": null
            },
            "StaffMemberMappingId": "456789",
            "PlacementDate": null,
            "LocationEmploymentType": null,
            "ContractStart": null,
            "ContractedHours": null,
            "ExpectedStartDate": null,
            "FirstShift": null,
            "LastShift": null,
            "LocationPreference": null,
            "SeesPatients": true
        }
    }
  ]
]

使用以下SQL查询提供空值

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'D:\pathtojsonfile\staff_location_test_data.json', SINGLE_CLOB) import

select
    Staff_level1.StaffKey, Staff_level1.Id
from   openjson (@JSON)
    with
    (
        StaffKey nvarchar(100),
        Id nvarchar(25)
        --[Staff] nvarchar(Max) as json
    )
    as Staff_level1

想知道我能不能帮你
1.“StaffKey”，“Id”，&“LastName”from Staff
1.“CompanyKey”，“Id”&“Address”from Location

ExtendedCredentialingFields中的“Name”、“Address”、“City”、“State”和“Zip”
我尝试使用交叉应用，但没有得到任何结果

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'D:\pathtojsonfile\staff_location_test_data.json', SINGLE_CLOB) import

select
    Staff_level2.StaffKey, Staff_level2.Id
from   openjson (@JSON)
    with
    (
        [Staff] nvarchar(Max) as json
    )
    as Staff_level1
cross apply openjson (Staff_level1.Staff)
with
(
    StaffKey nvarchar(100),
    Id nvarchar(25)
) as Staff_level2

我只是不太熟悉这种JSON格式，无法提取我正在寻找的元素。

JSON

来源：https://stackoverflow.com/questions/77318687/using-cross-apply-openjson-to-read-nested-json-flat-file

1条答案

按热度按时间

ufj5ltwl1#

问题中编写的查询仅适用于简单的非嵌套JSON数据。
为了准确地提取和管理复杂的多级嵌套JSON，我们应该使用CROSS APPLY来使用嵌套数据结构。这允许我们从JSON中的不同部分检索值，精确地定位所需的嵌套数据
试试这个

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'D:\pathtojsonfile\staff_location_test_data.json', SINGLE_CLOB) import

SELECT
    StaffData.*,
    LocationData.*,
    ExtCredData.*
FROM OPENJSON(@JSON)
CROSS APPLY OPENJSON([value])
WITH (
    Staff NVARCHAR(MAX) '$.Staff' AS JSON,
    Location NVARCHAR(MAX) '$.Location' AS JSON,
    ExtendedCredentialingFields NVARCHAR(MAX) '$.ExtendedCredentialingFields.LocationDetails' AS JSON
) AS JsonData
CROSS APPLY OPENJSON(JsonData.Staff)
WITH (
    StaffKey NVARCHAR(100),
    Id NVARCHAR(25),
    LastName NVARCHAR(50)
) AS StaffData
CROSS APPLY OPENJSON(JsonData.Location)
WITH (
    CompanyKey NVARCHAR(100),
    Id NVARCHAR(25),
    Address NVARCHAR(100)
) AS LocationData
CROSS APPLY OPENJSON(JsonData.ExtendedCredentialingFields)
WITH (
    Name NVARCHAR(100),
    AddressLine1 NVARCHAR(100),
    AddressLine2 NVARCHAR(100),
    City NVARCHAR(100),
    State NVARCHAR(50),
    Zip NVARCHAR(10)
) AS ExtCredData;

Query2

SELECT
    StaffData.*
FROM OPENJSON(@JSON)
CROSS APPLY OPENJSON([value])
WITH (
    Staff NVARCHAR(MAX) '$' AS JSON
) AS JsonData
CROSS APPLY OPENJSON(JsonData.Staff)
WITH (
        StaffKey NVARCHAR(100) '$.Staff.StaffKey',
        Id NVARCHAR(25) '$.Staff.Id',
        LastName NVARCHAR(50) '$.Staff.LastName',
        CompanyKey NVARCHAR(100) '$.Location.CompanyKey',
        Id NVARCHAR(100) '$.Location.Id',
        Address NVARCHAR(100) '$.Location.Address',
        Name NVARCHAR(100) '$.ExtendedCredentialingFields.LocationDetails.Name',
        AddressLine1 NVARCHAR(100) '$.ExtendedCredentialingFields.LocationDetails.AddressLine1',
        AddressLine2 NVARCHAR(100) '$.ExtendedCredentialingFields.LocationDetails.AddressLine2',
        City NVARCHAR(100) '$.ExtendedCredentialingFields.LocationDetails.City', 
        State NVARCHAR(50) '$.ExtendedCredentialingFields.LocationDetails.State',
        Zip NVARCHAR(10) '$.ExtendedCredentialingFields.LocationDetails.Zip'
    ) StaffData

演示https://dbfiddle.uk/-viKFaQf

赞(0）回复(0）举报 2023-10-21

我来回答

使用cross apply & OPENJSON读取嵌套的JSON平面文件

1条答案

相关问题

热门标签

最新问答