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

淩亂°似流年 2024-03-22 13:52 11阅读 0赞

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