使用交叉应用和 OPENJSON 读取嵌套的 JSON 平面文件-解网

问：

我有以下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 查询提供 null 值

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

想知道我是否可以在提取方面获得一些帮助

来自员工的“StaffKey”、“Id”和“LastName”
“CompanyKey”、“Id”和“Address”来自位置
来自 ExtendedCredentialingFields 的“名称”、“地址”、“城市”、“省/自治区/直辖市/自治区/直辖

我尝试使用CROSS APPLY，但没有得到任何结果

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 SQL-SERVER 分析层次结构

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;

查询 2

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

使用交叉应用和 OPENJSON 读取嵌套的 JSON 平面文件

Using cross apply & OPENJSON to read nested JSON flat file

评论

评论