SQL Server 2016 针对导入和导出 JSON 以及处理 JSON 字符串添加了内置支持,本文主要讲如何把数据转成json格式。
一、建表
创建tb_emp、tb_org两个表,插入数据。
CREATE TABLE tb_emp
(
emp_id INT NOT NULL PRIMARY KEY CLUSTERED,
emp_name VARCHAR(20),
)
INSERT INTO dbo.tb_emp
VALUES
(1, '张三'),
(2, '李四'),
(3, '王五'),
(4, '孙六'),
(5, '半泽直树');
CREATE TABLE tb_org
(
org_id INT NOT NULL PRIMARY KEY CLUSTERED,
org_name VARCHAR(30) NOT NULL,
parent_org_id INT NULL,
emp_id INT NULL
)
INSERT INTO tb_org(org_id,org_name,parent_org_id,emp_id)
VALUES
(1, '行长', NULL,1),
(2, '常务', 1,2),
(3, '专务', 1,3),
(4, '营业二部部长', 3,4),
(5, '营业二部次长', 4,5);
二、把数据转成json格式
有两种方式可以把数据转为json格式:for json path、for json auto,返回为json格式:[ {键 : 值} ... ]
使用 FOR JSON PATH 来保持对 JSON 输出格式的完全控制。 你可以创建包装对象并嵌套复杂属性。
下面的代码把parent_org_id 指定为 org.parent,emp_id指定为 org.emp,就会形成嵌套。
三、实现json的嵌套
使用 FOR JSON AUTO 来根据 SELECT 语句的结构自动格式化 JSON 输出。
SELECT v.*
FROM
(
SELECT o.org_id,
o.org_name,
e.emp_id,
e.emp_name
FROM tb_org o
INNER JOIN tb_emp e
ON o.emp_id = e.emp_id
WHERE o.org_id IN (1,2)
FOR JSON AUTO
)v(json_date)
返回的数据,经过格式化后显示如下:
注意其中箭头指向的 e,就是tb_emp表的数据,相当于是关联的子表的数据,会有嵌套。