表 json串用OPENJSON，在单个JSON数组列数据中查找一个数据点

huang714

已于 2023-05-08 14:27:50 修改

阅读量232

点赞数

文章标签： json javascript 前端

于 2023-05-08 14:24:13 首次发布

本文链接：https://blog.csdn.net/huang714/article/details/130558202

版权

文章展示了如何使用SQL的OPENJSON函数从名为InboundLog的表中的JSON数据中提取患者的社会安全号码(SSN)。创建了InboundLog表并插入了包含患者信息的数据，然后通过多个SELECT语句演示了如何查询和过滤JSON对象中的特定SSN。

摘要由CSDN通过智能技术生成

SELECT * FROM OPENJSON(InboundLog, Inbound_data.patient) WITH (Inbound_data.demographics.ssn = 77755777)

创建表

TABLE: InboundLog TABLE COLUMN: Inbound_data (JSON Array Data) {"patient": {"identifiers": [{"id": "5265635341", "idType": "Pancakes EMR"}], "demographics": {"firstName": "Spider", "lastName": "Man", "dob": "1930-01-06", "ssn": "777557777", "gender": "Male", "phone": "+18088675302", "email": "test1@example.com", "address": {"street1": "4762 Apple Street", "street2": "", "city": "Monroe", "state": "WI", "zip": "53566"}, "diagnoses": [{"codeType": "icd10Code", "code": "A01.00","isPrimary": false}, {"codeType": "icd10Code", "code": "W56.01XA","isPrimary": true}], "allergies": [{ "description": "smoke" }, { "description": "no allergy" }] } } }

create table dbo.InboundLog (
ID int not null identity(1,1),
Inbound_Data nvarchar(max)
);
insert dbo.InboundLog (Inbound_Data) values
(N'{"patient": { "demographics": { "ssn": "111111111 "} } }'),
(N'{"patient": { "demographics": { "ssn": "222222222 "} } }'),
(N'{"patient": { "demographics": { "ssn": "333333333 "} } }'),
(N'{"patient": { "demographics": { "ssn": "444444444 "} } }'),
(N'{"patient": { "demographics": { "ssn": "555555555 "} } }'),
(N'{"patient": { "demographics": { "ssn": "666666666 "} } }'),
(N'{
"patient": {
"identifiers": [
{
"id": "5265635341",
"idType": "Pancakes EMR"
}
],
"demographics": {
"firstName": "Spider",
"lastName": "Man",
"dob": "1930-01-06",
"ssn": "777557777",
"gender": "Male",
"phone": "+18088675302",
"email": "test1@example.com",
"address": {
"street1": "4762 Apple Street",
"street2": "",
"city": "Monroe",
"state": "WI",
"zip": "53566"
},
"diagnoses": [
{
"codeType": "icd10Code",
"code": "A01.00",
"isPrimary": false
},
{
"codeType": "icd10Code",
"code": "W56.01XA",
"isPrimary": true
}
],
"allergies": [
{
"description": "smoke"
},
{
"description": "no allergy"
}
]
}
}
}');

select ID, SSN
from InboundLog
cross apply openjson(Inbound_data, N'$.patient.demographics') with (
SSN varchar(10) '$.ssn'
);

select ID, SSN
from InboundLog
cross apply openjson(Inbound_data) with (
SSN varchar(10) N'$.patient.demographics.ssn'
);

select IL.*
from InboundLog IL
cross apply openjson(Inbound_data) with (
SSN varchar(10) N'$.patient.demographics.ssn'
)
where SSN = '777557777';

declare @param nvarchar(max);
set @param=N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}';

--判断是否是json
print iif(isjson(@param)>0, 'OK', 'NO');

--查询
print json_query(@param);

--取值
print json_value(@param, '$.info.address.town');
print json_value(@param, '$.info.tags[1]');
print json_query(@param, '$.info');
print json_query('["2020-1-8","2020-1-9"]');

--修改
print json_modify(@param, '$.info.address.town', 'London');

--其他案例

DROP TABLE IF EXISTS #MyTable CREATE TABLE #MyTable ( Id uniqueidentifier ,MyJsonText nvarchar(max)) INSERT INTO #MyTable(Id, MyJsonText) VALUES ('EDA4A604-59F4-4E4E-9C20-08D82314D8F6', '{"InitialCost":75.0,"OtherCost":50.0,"DatesOfVisit":["Wed, 26 Feb 2020 00:00:00 GMT","Fri, 20 Mar 2020 00:00:00 GMT"],"CatNumber":"PH123456"}') SELECT ISJSON(MyJsonText) FROM #MyTable -- Check JSON ok SELECT mt.Id ,mt_j.InitialCost ,mt_j.OtherCost ,mt_j.CatNumber FROM #MyTable mt CROSS APPLY OPENJSON(mt.MyJsonText) WITH ( InitialCost decimal(8,2) ,OtherCost decimal(8,2) ,CatNumber varchar(50)) as mt_j