sql server Json简单操作

操作环境：
SQL Server Management Studio 15.0.18424.0
SQL Server 管理对象 (SMO) 16.100.47021.0+7eef34a564af48c5b0cf0d617a65fd77f06c3eb1
Microsoft Analysis Services 客户端工具 15.0.19750.0
Microsoft 数据访问组件 (MDAC) 10.0.19041.2604
Microsoft MSXML 3.0 5.0 6.0
Microsoft .NET Framework 4.0.30319.42000
操作系统 10.0.19044

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

DECLARE @json1 NVARCHAR(500)='[   {     "id": 1,     "data": [       {         "testid": 1,         "testdata": "data1"       }     ]   },   {     "id": 2,     "data": [       {         "testid": 1,         "testdata": "data1"       }     ]   } ]'   DECLARE @newjson NVARCHAR(500)='{   "id": 3,   "data": [     {       "testid": 1,       "testdata": "data1"     },     {       "testid": 2,       "testdata": "data2"     }   ] }'   DECLARE @json2 NVARCHAR(500)='[   {     "id": 4,     "data": [       {         "testid": 1,         "testdata": "data1"       }     ]   },   {     "id": 5,     "data": [       {         "testid": 1,         "testdata": "data1"       }     ]   } ]'   --获取json中第一个对象的字段值 select JSON_Value(@json1,'$[0].id') --根据条件获取想要的字段值 SELECT JSON_VALUE([value],'$.id') as id FROM OPENJSON(@json1,'$') WHERE JSON_VALUE([value],'$.id') ='1' SELECT JSON_VALUE([value],'$.id') as id FROM OPENJSON(@json1,'$') WHERE JSON_VALUE([value],'$.id') in('1','2')   ----获取字符串格式字段值使用JSON_VALUE SELECT JSON_VALUE([value],'$.id') as id FROM OPENJSON(@json1,'$') --与上面一样的结果 SELECT * FROM OPENJSON(@json1) WITH(id int)   --获取json格式字段值需要使用JSON_QUERY SELECT JSON_QUERY([value],'$.data')  as id FROM OPENJSON(@json1,'$') WHERE JSON_VALUE([value],'$.id') in('1','2')   --json转table,data字段值需要转为json格式，否则数据为null SELECT * FROM OPENJSON(@json1) WITH(id int,[data] NVARCHAR(max) ) SELECT * FROM OPENJSON(@json1) WITH(id int,[data] NVARCHAR(max) '$.data'  AS JSON)   ----JSON数组增加单个json对象 SELECT @json1 = JSON_MODIFY(@json1,'append $',JSON_QUERY(@newjson)) SELECT @json1   ----合并两个JSON数组 SELECT @json1 = JSON_MODIFY(@json1,'append $',JSON_QUERY([value])) FROM OPENJSON(@json2) SELECT @json1   --修改数据1 set @json1 = JSON_MODIFY(@json1,'$[0].data[0].testdata','newdata') SELECT @json1 --修改数据2 set @json1 = JSON_MODIFY(@json1,'$[0].data','[]') SELECT @json1