SqlServer2016 JSON格式数据操作

JSON结构转为列表模式

DECLARE @json NVARCHAR(MAX) = N'
{
"001":null,
"002":"Duffy",
"003":123,
"004":456.789,
"005":["Dev","QA","PM"],
"006":{"Country":"Canada","Territory":"North America"}
}'
1.  SELECT * FROM OPENJSON(@json)

结果:

key    value                        type
001    NULL                        0
002    Duffy                         1
003    123                            2
004    456.789                     2
005    ["Dev","QA","PM"]     4
006    {"Country":"Canada","Territory":"North America"}    5

2. SELECT [key], value FROM OPENJSON(@json, '$."006"')

key           value
Country    Canada
Territory    North America

3. 判断是否为JSON字符串

SELECT CASE WHEN ISJSON(@json) > 0 THEN 'IS JSON.' ELSE 'NOT JSON.' END

 

4. 从一个JSON片段中抽取一个标量值

with tbl(strjson) as
(
  select  N'{
      "001":null,
      "002":"Duffy",
      "003":123,
      "004":456.789,
      "005":["Dev","QA","PM"],
      "006":{"Country":"Canada","Territory":"North America"}
    }'
)
SELECT JSON_VALUE(strjson, '$."006".Country') from tbl

结果:

Canada

5.  从一个JSON片段中抽取了一个对象或数组

with tbl(strjson) as
(
  select  N'{
      "001":null,
      "002":"Duffy",
      "003":123,
      "004":456.789,
      "005":["Dev","QA","PM"],
      "006":{"Country":"Canada","Territory":"North America"}
    }'
)
SELECT JSON_QUERY(strjson, '$."006"') from tbl

结果:

{"Country":"Canada","Territory":"North America"}

 

将查询结果输出JSON格式

with tbl(id, descr) as
(
  select  '001', 'AAA' union all
  select  '002', 'BBB' union all
  select  '003', 'CCC'
)

将SELECT语句的结果以JSON输出

select id, descr from tbl for json auto

结果:

[{"id":"001","descr":"AAA"},{"id":"002","descr":"BBB"},{"id":"003","descr":"CCC"}]

将SELECT语句的结果以JSON输出, 并为FOR JSON加上Root Key
select id, descr from tbl for json auto, root('list')

结果:

{"list":[{"id":"001","descr":"AAA"},{"id":"002","descr":"BBB"},{"id":"003","descr":"CCC"}]}

 

要自定义输出JSON格式的结构时，必须使用JSONPATH。

若SELECT的列中，存在列名名称相同时，必须用别名重命名

 JSONPATH输出

select id, descr from tbl for json path

 JSONPATH+ROOT输出

select id, descr from tbl for json path, root('list')

为NULL的数据在输出JSON时，会被忽略，若想要让NULL的字段也显示出来，可以加上选项INCLUDE_NULL_VALUES，该选项也适用于AUTO

select id, descr from tbl for json path, root('list'), INCLUDE_NULL_VALUES

 

带有层级关系的，可以增加节点，比如下面的SQL，增加了一个“SN”节点，把栏位SERNUM和CLIMAT放在里面

 

通过WITH选项，自定义输出列