OPENJSON 使用方法及案例

已于 2023-05-15 09:06:41 修改
阅读量697 收藏
点赞数
文章标签： json javascript 前端
于 2023-05-08 15:57:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/huang714/article/details/130561050
版权


DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  
   
SELECT * FROM  
 OPENJSON ( @json ) ; 
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 )
 
 
 
 SELECT * FROM OPENJSON('{"name" : null}');
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');
SELECT * FROM OPENJSON('[1,2,3]');
SELECT * FROM OPENJSON('[true,false]');
SELECT * FROM OPENJSON('{"cats":[{ "id":1, "name":"Fluffy"},{ "id":2, "name":"Scratch"}]}');
SELECT * FROM OPENJSON('[{"A":1,"B":0,"C":1}]');

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, '$.pets.cats');


ECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, '$.pets.cats[1]');


DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT * FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Sex]       varchar(6)      '$.sex', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );
    
    DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT 
    name,
    system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT * FROM OPENJSON(@json, ''$.pets.cats'') WITH  (
        [Cat Id]    int             ''$.id'',  
        [Cat Name]  varchar(60)     ''$.name'', 
        [Sex]       varchar(6)      ''$.sex'', 
        [Cats]      nvarchar(max)   ''$'' AS JSON 
    )',
    null,
    0
);


DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT * INTO JsonCats
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Sex]       varchar(6)      '$.sex', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );
    
    
    
    DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, 'lax $.pets.cows');


--严格模式 现在它处于
DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}'
SELECT * FROM OPENJSON(@json, 'strict $.pets.cows');

--具有显式结构的 OPEN
DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  
   
SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 ) 
 --在 WITH 子
DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT *
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Born]      date            'lax $.born', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );
    
    
 --
 DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT *
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Born]      date            'strict $.born', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );
    
    
--PENJSON 要求兼容性级别 130 ?
--查看?
--database为数据
 
USE database;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'database';  
GO
 
--示
 
USE model;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'model';  
GO


-------------1、-------------
declare @json as varchar(8000)
set @json='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]'
select * from openjson(@json);
--key    value    type
--0    {"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"}    5
--1    {"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}    5
-------------2、-------------
declare @json1 as varchar(8000)
set @json1='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]
'
select * from openjson(@json1)
with(
id varchar(10) '$.id',
Plies  int '$."myObject.Plies"',
Createtime datetime '$."myObject.Createtime"'
);
--id    Plies    Createtime
--1178    3    2020-07-21 14:33:18.480
--1179    3    2020-07-21 14:36:27.457
-------------3、-------------
declare @json2 as varchar(8000)
set @json2='{"myRoot":[
{"id":1178,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:33:18.480"}},
{"id":1179,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}}
]}'
select * from openjson(@json2,'$.myRoot')
with(
id varchar(10) ,
Plies  int '$.myObject.Plies',
Createtime datetime '$.myObject.Createtime'
);
--id    Plies    Createtime
--1178    3    2020-07-21 14:33:18.480
--1179    3    2020-07-21 14:36:27.457

declare @param nvarchar(max);
set @param = N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }';
 print iif(isjson(@param) > 0, 'OK', 'NO');
 print json_value(@param, '$.info.address.town');
print json_value(@param, '$.info.tags[1]');

print json_query(@param, '$.info');


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

DECLARE @txt1 varchar(max) = '{ "Rv0005": { "p.Glu540Asp": { "annotations":
[ { "type": "drug", "drug": "moxifloxacin", "literature": "10.1128/AAC.00825-17;10.1128/JCM.06860-11", "confers": "resistance" } ], 
"genome_positions": [ 6857, 6858, 6859 ] }, "p.Ala504Thr": { "annotations": [ { "type": "drug", "drug": "ciprofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "fluoroquinolones", "confers": "resistance" }, { "type": "drug", "drug": "levofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "moxifloxacin", "confers": "resistance" }, { "type": "drug", "drug": "ofloxacin", "confers": "resistance" } ], "genome_positions": [ 6749, 6750, 6751 ] }, "p.Ala504Val": { "annotations": [ { "type": "drug", "drug": "ciprofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "fluoroquinolones", "confers": "resistance" }, { "type": "drug", "drug": "levofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "moxifloxacin", "confers": "resistance" }, { "type": "drug", "drug": "ofloxacin", "confers": "resistance" } ], "genome_positions": [ 6749, 6750, 6751 ] } }, "Rv2043c": { "p.Thr100Ile": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "literature": "10.1128/JCM.01214-17", "confers": "resistance" } ], "genome_positions": [ 2288942, 2288943, 2288944 ] }, "p.Thr160Ala": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "literature": "10.1128/JCM.01214-17", "confers": "resistance" } ], "genome_positions": [ 2288762, 2288763, 2288764 ] }, "c.101_102insT": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "confers": "resistance" } ], "genome_positions": [ 2289140, 2289141 ] } } }'
--SELECT * FROM OPENJSON(@txt1, '$.Rv0005."p.Glu540Asp".genome_positions')


SELECT a.[key] as gene, b.[key] as mutations, c.*, d.value as genome_positions
FROM OPENJSON(@txt1) a
CROSS APPLY OPENJSON(a.value) b
CROSS APPLY OPENJSON(b.value,'$.annotations')
WITH ( 
    annotation_type nvarchar(100) '$.type'
    , annotation_drug nvarchar(100) '$.drug'
    , annotation_literature nvarchar(100) '$.literature'
    , annotation_confers nvarchar(100) '$.confers'
) c
CROSS APPLY OPENJSON(b.value,'$.genome_positions') d


DECLARE @txt1 varchar(max) = '{ "Rv0005": { "p.Glu540Asp": { "annotations":
[ { "type": "drug", "drug": "moxifloxacin", "literature": "10.1128/AAC.00825-17;10.1128/JCM.06860-11", "confers": "resistance" } ], 
"genome_positions": [ 6857, 6858, 6859 ] }, "p.Ala504Thr": { "annotations": [ { "type": "drug", "drug": "ciprofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "fluoroquinolones", "confers": "resistance" }, { "type": "drug", "drug": "levofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "moxifloxacin", "confers": "resistance" }, { "type": "drug", "drug": "ofloxacin", "confers": "resistance" } ], "genome_positions": [ 6749, 6750, 6751 ] }, "p.Ala504Val": { "annotations": [ { "type": "drug", "drug": "ciprofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "fluoroquinolones", "confers": "resistance" }, { "type": "drug", "drug": "levofloxacin", "confers": "resistance" }, { "type": "drug", "drug": "moxifloxacin", "confers": "resistance" }, { "type": "drug", "drug": "ofloxacin", "confers": "resistance" } ], "genome_positions": [ 6749, 6750, 6751 ] } }, "Rv2043c": { "p.Thr100Ile": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "literature": "10.1128/JCM.01214-17", "confers": "resistance" } ], "genome_positions": [ 2288942, 2288943, 2288944 ] }, "p.Thr160Ala": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "literature": "10.1128/JCM.01214-17", "confers": "resistance" } ], "genome_positions": [ 2288762, 2288763, 2288764 ] }, "c.101_102insT": { "annotations": [ { "type": "drug", "drug": "pyrazinamide", "confers": "resistance" } ], "genome_positions": [ 2289140, 2289141 ] } } }'
--SELECT * FROM OPENJSON(@txt1, '$.Rv0005."p.Glu540Asp".genome_positions')


SELECT a.[key] as gene, b.[key] as mutations, c.*, d.value as genome_positions
FROM OPENJSON(@txt1) a
CROSS APPLY OPENJSON(a.value) b
CROSS APPLY OPENJSON(b.value,'$.annotations')
WITH ( 
    annotation_type nvarchar(100) '$.type'
    , annotation_drug nvarchar(100) '$.drug'
    , annotation_literature nvarchar(100) '$.literature'
    , annotation_confers nvarchar(100) '$.confers'
) c
CROSS APPLY OPENJSON(b.value,'$.genome_positions') d


DECLARE @txt1 varchar(max) = '[
  [
    {
      "ControlType": "60",
      "ControlCaption": "1034036",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "19/10/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1034036",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "18/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "373.64",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": true,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1034036"
      }
    }
  ],
  [
    {
      "ControlType": "60",
      "ControlCaption": "1035375",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "27/10/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1035375",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "26/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "20.24",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": false,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1035375"
      }
    }
  ],
  [
    {
      "ControlType": "60",
      "ControlCaption": "1036326",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "02/11/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "CD",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "1036326",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "02/12/2021",
      "ControlStyle": "width:100%; padding:0px;",
      "IsTableItem": true
    },
    {
      "ControlType": "60",
      "ControlCaption": "57.24",
      "ControlStyle": "width:100%; padding:0px; text-align: right;",
      "IsTableItem": true
    },
    {
      "ControlType": "20",
      "LabelStyle": "display:none",
      "CheckboxValue": false,
      "ControlStyle": "text-align:left",
      "IsTableItem": true,
      "CheckboxIconSize": "15px",
      "Misc": {
        "Company": "TTT",
        "ReferenceDuplicate": "1",
        "Type": "CD",
        "ReferenceNumber": "1036326"
      }
    }
  ]
]';
select A2.Company,
       A2.ReferenceDuplicate,
       A2.Type,
       A2.ReferenceNumber
from openjson(@txt1) as A1
  cross apply openjson(A1.value)
    with (
           CheckboxValue bit,
           Company varchar(100) '$.Misc.Company',
           ReferenceDuplicate int '$.Misc.ReferenceDuplicate',
           Type varchar(100) '$.Misc.Type',
           ReferenceNumber int '$.Misc.ReferenceNumber'
         ) as A2
where A2.CheckboxValue = 1;


select json_value(A2.value, '$.Misc.Company') as Company,
       json_value(A2.value, '$.Misc.ReferenceDuplicate') as ReferenceDuplicate,
       json_value(A2.value, '$.Misc.Type') as Type,
       json_value(A2.value, '$.Misc.ReferenceNumber') as ReferenceNumber
from openjson(@txt1) as A1
  cross apply openjson(A1.value) as A2
where json_value(A2.value, '$.CheckboxValue') = 'true';


DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'{"Version":"3.0.202303.1","BaseInfo":{"DefinedProp":[{"Key":"baioqin","Val":"","Sort":null,"IsShow":1,"IsSys":0},{"Key":"钱包","Val":"","Sort":null,"IsShow":1,"IsSys":0}],"BuildTime":"2023-05-08","UserName":"临床药师-hwm001","MedNo":"0000164751_20230508","HospitalNo":"0000164751","PatientName":"田义","Nationality":"","Ethnic_group":"","BirthDate":"1963-09-11","Sex":"男","Age":"51","BedNo":"282112-0","StartDate":"2014-07-07","EndDate":"2014-07-18","DeptName":"泌尿外科","DoctorName":"刘世博","Pharmacist":"临床药师-hwm001","WorkAddress":"","Address":"","Telephone":"13940076300","Postcode":"","PayClass":"市医保","Education":"","Marital_Status":"","In_Diagnosis":"输尿管恶性肿瘤","Out_Diagnosis":"独立的多个部位的（原发性）恶性肿瘤(治愈),睾丸恶性肿瘤(治愈),结肠恶性肿瘤,输尿管恶性肿瘤(治愈),为肿瘤化学治疗疗程(治愈),胃恶性肿瘤,移行细胞癌NOS(治愈)","agevalue":59.0,"IAge":null,"mhiscode":1,"Height":"173","Weight":"100","Abo_Type":"","Rh_Type":""},"InhospCheckup":{"DefinedProp":[{"Key":"体脂","Val":"","Sort":null,"IsShow":1,"IsSys":0}],"BMI":"33.41","BSA":"2.182","Temperature":"","Pulse":"","Breathe":"","BloodPressure":"","Height":"173","Weight":"100","Abo_Type":"","Rh_Type":""},"InhospLab":{"DefinedProp":[{"Key":"自定义","Val":"","Sort":null,"IsShow":1,"IsSys":0}],"eGFR":"","CCR":"","ScR":"151.00g/L","ALT":"","AST":"","Pct":"","CReactive":"","Esr":""},"InhospOther":{"DefinedProp":[],"AdvancedAge":0,"LiverFunction":null,"RenalFunction":null,"BadHabits":null},"MedPharmHistory":{"DefinedProp":[],"MDescript":"","MhisDescript":"","Elapse":"","InfoSource":"","InfoSourceDes":"","TreatRecords":[],"Family":"","Allergen":[],"MedicationSituation":"","Adr":[],"InDiagnose":null,"OutDiagnose":null},"LisLab":[],"YaoMinWswLab":[],"LisExam":[],"InitTreatmentPlan":{"InitialDrugDBGrid":[{"cid":"18805776","grouptag":"15514533","groupstate":null,"is_temp":"长期","drugname":"注射用胸腺五肽","singledose":"10.00","doseunit":"mg","frequency":"BID","routename":"im","startdate":"2014-07-08 08:27:42","enddate":"2014-07-17 23:59:59","PAEndDateTime":"","singledoseunit":"10.00mg","routefrequency":" im BID","druguniquecode":"Y00000003100"}],"InitialProgramme":"一、诊断依据及病史特点<br/>1.病史：<br/>2.主诉：<br/>3.查体：<br/>4.检查：<br/>5.其他：<br/>二、治疗原则","InitialCustody":""},"OtherTreatmentPlan":{"OtherTDrugDBGrid":[],"OtherMainDrugSummary":""},"TreatmentLog":[{"DefinedProp":[],"date":"2014-07-08","inhospDays":2,"Illness":"<div class=\"ql-editor\"><p>说的 </p></div>","treatmentKey":"<div class=\"ql-editor\"><p>撒 </p></div>","drugNew":[{"cid":"18805776","grouptag":"15514533","groupstate":null,"is_temp":"长期","drugname":"注射用胸腺五肽","singledose":"10","doseunit":"mg","frequency":"BID","routename":"im","startdate":"2014-07-08 08:27:42","enddate":"2014-07-17 23:59:59","PAEndDateTime":"2014-07-17 23:59:59","singledoseunit":"10mg","routefrequency":" im BID","druguniquecode":"Y00000003100"},{"cid":"18805667","grouptag":"15514413","groupstate":null,"is_temp":"临时","drugname":"0.9%氯化钠注射液","singledose":"500","doseunit":"ml","frequency":"ST","routename":"膀胱灌注","startdate":"2014-07-08 08:26:07","enddate":"2014-07-08 08:26:07","PAEndDateTime":"2014-07-08 08:26:07","singledoseunit":"500ml","routefrequency":" 膀胱灌注 ST","druguniquecode":"Y00000000505"},{"cid":"18816470","grouptag":"15527563","groupstate":1,"is_temp":"临时","drugname":"0.9%氯化钠注射液","singledose":"250","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"2014-07-08 13:23:25","singledoseunit":"250ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003416"},{"cid":"18816471","grouptag":"15527563","groupstate":3,"is_temp":"临时","drugname":"5%葡萄糖注射液","singledose":"250","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"2014-07-08 13:23:25","singledoseunit":"250ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003421"},{"cid":"18816472","grouptag":"15527563","groupstate":2,"is_temp":"临时","drugname":"氟尿嘧啶注射液","singledose":"0.5","doseunit":"g","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"2014-07-08 13:23:25","singledoseunit":"0.5g","routefrequency":" iv.dri ST","druguniquecode":"Y00000003327"}],"drugStop":[{"cid":"18816470","grouptag":"15527563","groupstate":1,"is_temp":"临时","drugname":"0.9%氯化钠注射液","singledose":"250.00","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"250.00ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003416"},{"cid":"18816471","grouptag":"15527563","groupstate":3,"is_temp":"临时","drugname":"5%葡萄糖注射液","singledose":"250.00","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"250.00ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003421"},{"cid":"18816472","grouptag":"15527563","groupstate":2,"is_temp":"临时","drugname":"氟尿嘧啶注射液","singledose":"0.50","doseunit":"g","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"0.50g","routefrequency":" iv.dri ST","druguniquecode":"Y00000003327"}],"DrugHistory":"<div class=\"ql-editor\"><p>    阿萨 </p></div>"},{"DefinedProp":[],"date":"2014-07-12","inhospDays":6,"Illness":"<div class=\"ql-editor\"><p>阿萨 </p></div>","treatmentKey":"<div class=\"ql-editor\"><p>啊 </p></div>","drugNew":[{"cid":"18816470","grouptag":"15527563","groupstate":1,"is_temp":"临时","drugname":"0.9%氯化钠注射液","singledose":"250.00","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"250.00ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003416"},{"cid":"18816471","grouptag":"15527563","groupstate":3,"is_temp":"临时","drugname":"5%葡萄糖注射液","singledose":"250.00","doseunit":"ml","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"250.00ml","routefrequency":" iv.dri ST","druguniquecode":"Y00000003421"},{"cid":"18816472","grouptag":"15527563","groupstate":2,"is_temp":"临时","drugname":"氟尿嘧啶注射液","singledose":"0.50","doseunit":"g","frequency":"ST","routename":"iv.dri","startdate":"2014-07-08 13:23:25","enddate":"2014-07-08 13:23:25","PAEndDateTime":"","singledoseunit":"0.50g","routefrequency":" iv.dri ST","druguniquecode":"Y00000003327"}],"drugStop":[],"DrugHistory":"<div class=\"ql-editor\"></div>"}],"SummaryOfDrugTherapy":""}';  
    select 1 as id ,json_value(@json, '$.BaseInfo.UserName') as UserName,
     json_value(@json, '$.BaseInfo.MedNo') as MedNo,
     json_value(@json, '$.BaseInfo.HospitalNo') as HospitalNo,          
    json_value(@json, '$.TreatmentLog[0].date');
    
    
    
    
    ALTER DATABASE [PASSPA2DB] SET COMPATIBILITY_LEVEL = 130

huang714
关注
使用 SQL 处理 NoSQL 数据
Tony.Dong的专栏
09-22 498
上一篇我们介绍了一些常见的 SQL 优化技巧，包括创建合适的索引和各种查询语句的优化。 今天我们来学习如何在关系数据库中存储 NoSQL 数据（JSON 文档），以及使用 SQL 实现数据行与 JSON 文档的相互转换。 什么是 JSON？ JSON（JavaScript Object Notation、JavaScript 对象表示法）是一种轻量级的数据交换格式，采用完全独立于编程语言的文本格式...
SQL中OPENJSON函数JSON到表行数据转换
橙子撸代码专栏
01-12 9705
SQL OPENJSON函数 对JSON数据转表行使用，SQL OPENJSON函数介绍参考https://docs.microsoft.com/zh-cn/sql/relational-databases/json/convert-json-data-t
继续说一下openjson 以及 json path 的使用 (2)
weixin_30539835的博客
04-18 170
在openjson 里面，其实是可以把数据类型array里面的值遍历出来的，举个栗子 declare @v nvarchar(500) = N' {"name":"test", "obj":{"arr":[1,"ofao",3,4,5]} } ' select * from openjson(@v) ／＊ key value type name test...
OPENJSON 简单应用
我想我是海 冬天的大海 心情随风轻摆
03-02 668
--适合于 SQL Server2016+ USE [tempdb] GO CREATE OR ALTER PROC [dbo].[Proc_Test] @json NVARCHAR(MAX) = '[{"GoodsId": 3, "Quantity": 10}, {"GoodsId": 8,"Quantity": 20}]' AS BEGIN SET NOCOUNT ON; SELECT goodsId,quantity FROM OPENJSON(@json) WITH ( goods.
OpenJson做最易用的高性能C++json解析器
linyouhappy的博客
02-12 391
OpenJson是世界上最好用的高性能C++json解析器，非常简单易用，解析速度超快，可以解析超过1GB以上的json文件。
SqlServer2008使用OpenJson函数报错:关键字 ‘with‘ 附近有语法错误。如果此语句是公用表表达式xmlnamespaces 子句或者更改跟踪上下文子句那么前一个语句必须以分号结尾
咏絮
11-30 3235
SqlServer2008使用OpenJson函数报错:关键字 ‘with‘ 附近有语法错误。如果此语句是公用表表达式xmlnamespaces 子句或者更改跟踪上下文子句那么前一个语句 原因：数据库2008版本不支持Json函数，因此需要把服务器的兼容级别调整了2016或以上。 解决方法：选中数据库->右键并选择属性->选项->修改兼容性级别为2016
sqlserver json字段如何建立，如何使用，请给详细的案例
07-20
下面是在SQL Server中创建和使用JSON字段的详细步骤和案例： 1. 创建表时定义JSON字段: 你可以在创建表时使用`nvarchar(max)`或`varchar(max)`数据类型来定义一个JSON字段。例如，假设你有一个表名为`users`，其中...
实用案例 - SqlServer数据库数据无法读取解决方法
weixin_33720956的博客
05-02 2580
1、数据库故障概述SQL server数据库的数据无法被读取。2、故障分析SQL server数据库文件无法被读取，是由于底层File Record被截断为0，无法找到文件开头，数据表结构也被损坏。镜像文件的前面80M左后的空间，还有中间一部分被覆盖掉，导致系统表被损坏，所以无法读取，考虑用自动备份文件来提取表结构。日志中的操作记录：由于系统表被损坏，有大量数据表的结构无法被确定，只能靠工程师根据...
sp_spaceused_如何使用sp_spaceused和PowerShell捕获数据库使用情况统计信息
culuo4781的博客
07-21 1441
sp_spaceused Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique ch...
将PowerShell输出写入SQL Server表的6种方法
culuo4781的博客
07-23 1692
PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework an...
sqlserver通过OPENJSON转换 json数据
slilx的博客
11-15 3741
行集函数可将 JSON 文本转换为一组行和列。使用 OPENJSON 将 JSON 集合转换为行集后，可以在返回的数据上运行任意 SQL 查询或将其插入到 SQL Server 表中。函数采用单个 JSON 对象或 JSON 对象的集合，并将其转换为一行或多行。可以添加可选的 WITH 子句来提供显式定义输出结构的架构。
表 json串用OPENJSON，在单个JSON数组列数据中查找一个数据点
huang714的专栏
05-08 232
创建表(N'{],},},],},}');
【OPENJSON】MS中的OPENJSON()表值函数
weixin_46817408的博客
03-11 1043
OPENJSON()表值函数的语法和使用
SQLServer中OPENJSON函数的用法
最新发布
weixin_42525264的博客
06-28 202
OPENJSON 要求兼容性级别 130，即SQLSERVER2016以上版本1、默认输出的 OPENJSON在不提供结果的显式架构的情况下使用 OPENJSON 函数时（在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：输入对象中属性的名称（或输入数组中元素的索引）。属性或数组元素的值。类...
SQL Server2016解析Json格式数据
qq_18932003的博客
10-13 1808
sqlserver
sqlserver使用json数据
hdhdhdhdhdhdhdh的博客
11-16 1478
通过确保插入的 JSON 数据是有效的，并符合 MySQL 的 JSON 格式要求，我们可以成功地将 JSON 数据插入到 MySQL 表中。在sqlserver 中存储json ，需要用字符串类型进行存储，一般用nvarchar()或 varchar()进行存储，不要用text进行存储，用text时候，json的函数不支持。（5）Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。
关于SQL Server 2017中使用json传参时解析遇到的多层解析问题
weixin_30752377的博客
06-25 373
开发新的系统，DB部分使用了SQL Server从2016版开始自带的Json解析方式。 用了快半年，在个人项目，以及公司部分项目上使用了，暂时还没遇到大的问题，和性能问题。 今天在解析Json的多级参数遇到了点小问题，在此记录一下： 传参内容如下： 1 declare @json varchar(max)=N'{"Customer":36,"Positions":[{"ID":0,"...
cross apply和表值函数进行关联查询
Discovery的专栏
04-04 4766
目前场景是有一个兴趣爱好类型表和一个员工爱好表，员工爱好表中的爱好一列，如果这个员工有多个爱好，那么就将这些爱好放到一条记录当中，爱好之间用逗号隔开，现在需求是要将每个员工的这些爱好分隔成一条条记录查询出来，从而可以例如做员工爱好统计等应用。
openjson怎么用
04-30
OpenJSON 是 SQL Server 2016 引入的一种 T-SQL 函数，用于解析 JSON 格式的数据。以下是使用 OpenJSON 函数的基本示例： 假设有一个名为 json_data 的 JSON 格式字符串变量，其中包含一个名为 "name" 的键和它的值 "Alice"： ``` DECLARE @json_data NVARCHAR(MAX) = '{"name": "Alice"}'; ``` 我们可以使用 OpenJSON 函数将它解析成一张表： ``` SELECT * FROM OPENJSON(@json_data); ``` 输出结果如下： ``` key value type ---- -------------- ------ name Alice 1 ``` 其中，key 代表 JSON 中的键名，value 是对应的值，type 是值的数据类型（1 代表字符串）。 如果 JSON 数据中有多个键值对，我们可以使用 WITH 子句来指定每个键值对的名称及其数据类型。例如，对于以下 JSON 数据： ``` DECLARE @json_data NVARCHAR(MAX) = '{"name": "Alice", "age": 20}'; ``` 我们可以使用以下代码解析它： ``` SELECT name, age FROM OPENJSON(@json_data) WITH ( name NVARCHAR(50) '$.name', age INT '$.age' ); ``` 输出结果如下： ``` name age ---- --- Alice 20 ``` 在 WITH 子句中，我们通过指定路径表达式来获取对应键的值，例如 `$.name` 表示获取键名为 "name" 的值。 除了解析 JSON 数据，OpenJSON 还支持将 JSON 数据直接插入表中，以及将表数据转换为 JSON 格式。具体使用方法可以参考官方文档。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值