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

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