英文:
Why am I unable to access subnodes of my JSON using SQL?
问题
我对这个问题感到有点疯狂，我确信对于不是一直盯着它看了一个小时的人来说，这个问题应该很容易发现...
我有以下的JSON（'Details' 还有更多的示例，但两个应该足够解决这个问题）：
[
{
"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，然后将 Details 作为 JSON 传递并交叉应用它，但我认为上面的首选方法也应该起作用？
谢谢
Ant
英文:
I'm going a bit mad with this one, and I'm sure it'll be something easily spotted by someone not staring at it for the last hour...
I have the following JSON (there are more examples of 'Details' but two should suffice for this question:
[
{
"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
}
]
}
]
My understanding was that if I wanted to get eg. MonthDetails.Details >> ServiceUserName, SUID, etc, I could use the following code:
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'
)
However, all that happens is that one record is creating one entry with NULL in every column. I'm aware that I can just do $.MonthDetails and then pass the Details as JSON through and cross apply it, but I thought the preferred method above should work too?
Thanks
Ant
答案1
得分: 1
以下是您要翻译的内容：
-- 对于数组中的单个对象和多个详细信息
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;
请注意，我已将SQL代码中的HTML实体编码进行了还原。
英文:
for single objects in arrays and multiple Details
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
);
for any number of objects in the arrays (adjust for the rest of the elements/objects)
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;
答案2
得分: 1
以下是您要翻译的部分：
在您的示例中出现意外结果的原因是您有一个JSON结构，其中包含嵌套的JSON数组，而您没有一个可以使用OPENJSON()和路径'$.MonthDetails.Details'进行解析的对象或数组。在这种情况下，您需要使用附加的APPLY操作符和OPENJSON()调用来解析嵌套的JSON数组。
如果您有以下格式的JSON（整个JSON和“MonthDetails”键都是JSON对象，而不是JSON数组），则问题中的语句将起作用：
DECLARE @json nvarchar(max)
SELECT @json = N'{
"MonthDetails": {
"Month": 1,
"MonthName": "January 2020",
"Details": [
{
"ServiceUserName": "Resident 1"
},
{
"ServiceUserName": "Resident 2"
}
],
"NumberOfDays": 0
}
}'
SELECT *
FROM OPENJSON(@json, '$.MonthDetails.Details') WITH (
ServiceUserName nvarchar(100) '$.ServiceUserName'
/*, ... other columns */
)
要正确解析JSON，您应该使用以下语句：
DECLARE @json nvarchar(max)
SELECT @json = N'[
{
"MonthDetails": [
{
"Month": 1,
"MonthName": "January 2020",
"Details": [
{
"ServiceUserName": "Resident 1"
},
{
"ServiceUserName": "Resident 2"
}
],
"NumberOfDays": 0
}
]
}
]'
SELECT j2.*
FROM OPENJSON(@json, '$[0].MonthDetails') WITH (
Details nvarchar(max) '$.Details' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Details, '$') WITH (
ServiceUserName nvarchar(100) '$.ServiceUserName'
/*, ... other columns */
) j2
英文:
The reason for this unexpected result in your example is the fact, that you have a JSON structure, that includes nested JSON arrays and you don't have an object or array, that can be parsed with OPENJSON() and path like '$.MonthDetails.Details'. In this case, you need to use an additional APPLY operator and OPENJSON() call to parse the nested JSON arrays.
The statement in the question will work, if you have a JSON with the following format ( the whole JSON and the "MonthDetails" key are JSON objects, not JSON arrays):
DECLARE @json nvarchar(max)
SELECT @json = N'{
"MonthDetails": {
"Month": 1,
"MonthName": "January 2020",
"Details": [
{
"ServiceUserName": "Resident 1"
},
{
"ServiceUserName": "Resident 2"
}
],
"NumberOfDays": 0
}
}'
SELECT *
FROM OPENJSON(@json, '$.MonthDetails.Details') WITH (
ServiceUserName nvarchar(100) '$.ServiceUserName'
/*, ... other columns */
)
To parse the JSON correctly, you should use the following statement:
DECLARE @json nvarchar(max)
SELECT @json = N'[
{
"MonthDetails": [
{
"Month": 1,
"MonthName": "January 2020",
"Details": [
{
"ServiceUserName": "Resident 1"
},
{
"ServiceUserName": "Resident 2"
}
],
"NumberOfDays": 0
}
]
}
]'
SELECT j2.*
FROM OPENJSON(@json, '$[0].MonthDetails') WITH (
Details nvarchar(max) '$.Details' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Details, '$') WITH (
ServiceUserName nvarchar(100) '$.ServiceUserName'
/*, ... other columns */
) j2
答案3
得分: 0
由于MonthDetails内有两个数组，我假设您想要两行数据。因此，您实际上可能想要的是这样的查询：
SELECT D.*
-- 获取第一个数组，使用[0]，如AlwaysLearning所指出的
FROM OPENJSON(@JSON, '$[0].MonthDetails')
WITH (Details nvarchar(MAX) AS JSON) OJ
-- 现在解析内部数据
CROSS APPLY OPENJSON(OJ.Details)
WITH (ServiceUserName nvarchar(100) '$.ServiceUserName',
ServiceUserLastName nvarchar(100) '$.ServiceUserName',
SUID nvarchar(150) '$.SUID', -- 这不应该是uniqueidentifier吗？
-- 为什么使用nvarchar(150)，而uniqueidentifer只有36个字符，可以使用varchar来显示？
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', -- 为什么使用nvarchar，当它只能是true或false？
NeedsBarrierNursing nvarchar(5) '$.NeedsBarrierNursing',-- 为什么使用nvarchar，当它只能是true或false？
IsOpen nvarchar(5) '$.IsOpen',
DaysToClear nvarchar(5) '$.DaysToClear', -- 这不应该是整数吗？
SURecordOpen nvarchar(5) '$.SURecordOpen') D; -- 为什么使用nvarchar，当它只能是true或false？
请注意，我强烈建议更改为正确的数据类型，我已经在评论中提到了这一点。
<details>
<summary>英文:</summary>
As you have 2 arrays inside `MonthDetails`, I assume you want 2 rows. Therefore it seems like what you are actually after might be this:
SELECT D.*
--Get first array, using [0], as pointed out by AlwaysLearning
FROM OPENJSON(@JSON, '$[0].MonthDetails')
WITH (Details nvarchar(MAX) AS JSON) OJ
--Now parse the inner data
CROSS APPLY OPENJSON(OJ.Details)
WITH (ServiceUserName nvarchar(100) '$.ServiceUserName',
ServiceUserLastName nvarchar(100) '$.ServiceUserName',
SUID nvarchar(150) '$.SUID', --Should this not be a uniqueidentifier?
--Why an nvarchar(150) when a uniqueidentifer is a 36 characters which all can be displayed with a varchar?
InfectionType nvarchar(30) '$.InfectionType',
DateStarted nvarchar(10) '$.DateStarted', --Should this not be a date?
DateCleared nvarchar(10) '$.DateCleared', --Should this not be a date?
NextReviewDate nvarchar(10) '$.NextReviewDate', --Should this not be a date?
AntibioticsStartDate nvarchar(10) '$.AntibioticsStartDate', --Should this not be a date?
AntibioticsEndDate nvarchar(10) '$.AntibioticsEndDate', --Should this not be a date?
AcquiredOnAdmission nvarchar(5) '$.AcquiredOnAdmission', --Why an nvarchar when it can only be true or false?
NeedsBarrierNursing nvarchar(5) '$.NeedsBarrierNursing',--Why an nvarchar when it can only be true or false?
IsOpen nvarchar(5) '$.IsOpen',
DaysToClear nvarchar(5) '$.DaysToClear', --Should this not be an int?
SURecordOpen nvarchar(5) '$.SURecordOpen') D; --Why an nvarchar when it can only be true or false?
Note I strongly recommend changing to the correct data type, which i have commented on.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论