Create JSON in SQL Server (FOR JSON)

2021-08-11

0. 前言

工作中经常需要用 sql 来处理 json，常见的操作有以下几种：

创建 JSON - FOR JSON
判断 JSON - ISJSON()
读取 JSON - JSON_VALUE(), JSON_QUERY(), OPENJSON()
修改 JSON - JSON_MODIFY()

今天来讲讲创建 JSON 的 FOR JSON 功能。

Reference: Work with JSON data - SQL Server | Microsoft Docs

1. 准备工作

在 localdb 里面创建一个数据库，然后执行下面的 sql 来创建 table + 插入测试数据。

CREATE TABLE [dbo].[Employee](
	[Id] [int] NOT NULL IDENTITY PRIMARY KEY,
	[EmpId] [int] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[Status] [nvarchar](50) NOT NULL,
	[ManagerId] [int] NOT NULL,
	[ManagerName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO


insert into dbo.[Employee] values (1, GETUTCDATE() - 1, 'Active', 1000, 'David')
insert into dbo.[Employee] values (1, GETUTCDATE() - 3, 'Active', 2000, 'Bob')
insert into dbo.[Employee] values (1, GETUTCDATE() - 5, 'Active', 3000, 'Az')
insert into dbo.[Employee] values (2, GETUTCDATE() - 30, 'Active', 1000, 'David')
insert into dbo.[Employee] values (2, GETUTCDATE() - 20, 'Inactive', 4000, 'Grace')
insert into dbo.[Employee] values (3, GETUTCDATE() - 2, 'Active', 4000, 'Grace')
insert into dbo.[Employee] values (3, GETUTCDATE() - 1, 'Active', 3000, 'Az')
insert into dbo.[Employee] values (4, GETUTCDATE() - 1, 'Active', 3000, 'Az')

select * from dbo.Employee

2. For JSON Auto 和 For JSON Path 的使用

2.1 区别是什么？

A query that uses the FOR JSON AUTO option must have a FROM clause.

When you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables.

Use the PATH option to maintain control over the output.

简单来说，FOR JSON PATH 是 FOR JSON AUTO 的 superset，所有能用 FOR JSON AUTO 的都能用 FOR JSON PATH 替换。

所以下面的例子我们一律使用 FOR JSON PATH。

2.2 Demo1

在整个 table 上使用 FOR JSON PATH，得到一个把每行的 column name 作为 key，cell value 作为 value 的 json object 组成的 json array。

select * from dbo.Employee
for json path

2.3 Demo2

不管我们选择的是整个表格还是表格中的一行数据，FOR JSON PATH 都是默认返回 JSON ARRAY.

select * from dbo.Employee
where id = 5
for json path

2.4 Demo3 - WITHOUT_ARRAY_WRAPPER

假如我们想去掉 array 的 []，可以加上 WITHOUT_ARRAY_WRAPPER，但这么一来它就不是一个 valid json 了。

select * from dbo.Employee
for json path, WITHOUT_ARRAY_WRAPPER

select ISJSON('[{"Id":1,"EmpId":1},{"Id":2,"EmpId":1}]')
select ISJSON('{"Id":1,"EmpId":1},{"Id":2,"EmpId":1}') -- 去掉 wrapper 之后的结果

2.5 Demo4 - ROOT

加上 root 可以让我们得到一个 root 为 key，array 为 value 的 json object。

select * from dbo.Employee
for json path, root ('abc')

不过 WITHOUT_ARRAY_WRAPPER 和 root('abc') 不能同时出现哦~

2.6 Demo5

如果我们想要的 output 是每行有一个单独 json 的表格，那么 For JSON Path 可以帮助我们轻松达成这个目标。

select empid,
	(select [CreatedAt], [Status] for json path) as StatusJson
from dbo.Employee

2.7 Demo6 - inner query

稍微复杂一点的 inner query 可以让我们更好地把数据合并在一起。

select id,
	(
		select [CreatedAt], [Status]
			from dbo.Employee e1
			where e1.id = e2.id
			for json path
	) as StatusJson
from dbo.Employee e2

select empid,
	(
		select [CreatedAt], [Status]
			from dbo.Employee e1
			where e1.empid = e2.empid
			for json path
	) as StatusJson
from dbo.Employee e2
group by empid

3. 实例

问题

假如你的源数据是 json object，你需要把多个 json object 合并到一起变成一个 json array，要怎么做呢？

drop table if exists #sourcedata

select empid,
	(select [CreatedAt], [Status] for json path, without_array_wrapper) as StatusJson
into #sourcedata
from dbo.Employee

select * from #sourcedata

目标

我们期望得到的结果是这样：

尝试 1

我们直接用 for json path，发现效果并不理想。。首先我们不想要 column name，其次 json 还有一些 escape character。

Note: without_array_wrapper 是去掉最外层的 array 符号，root 在这里也不适用，因为我们最终想得到的还是个 array。

select empid,
		(
			select StatusJson
				from #sourcedata s2
				where s2.EmpId = s1.EmpId
				for json path
		) as CombinedJson
from #sourcedata s1
group by empid

尝试 2

既然我们希望把 column name 去掉，而 column name 是 key，那么我们是不是可以 unwrap 每个 json object，只取它的两个 field 呢？

select empid,
		(
			select
				JSON_value(StatusJson, '$.CreatedAt') as [CreatedAt],
				JSON_value(StatusJson, '$.Status') as [Status]
				from #sourcedata s2
				where s2.EmpId = s1.EmpId
				for json path
		) as CombinedJson
from #sourcedata s1
group by empid

4. 结语

今天这篇主要讲了 FOR JSON 的使用，总结一下：

能用 for json auto 的都能用 for json path，前者是后者的子集；
for json 默认返回一个 json array，我们可以用 root 让它变成一个 json object；

附：完整 script

-- 1. data prep
DROP TABLE IF EXISTS [dbo].[Employee]
CREATE TABLE [dbo].[Employee](
	[Id] [int] NOT NULL IDENTITY PRIMARY KEY,
	[EmpId] [int] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[Status] [nvarchar](50) NOT NULL,
	[ManagerId] [int] NOT NULL,
	[ManagerName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

insert into dbo.[Employee] values (1, GETUTCDATE() - 1, 'Active', 1000, 'David')
insert into dbo.[Employee] values (1, GETUTCDATE() - 3, 'Active', 2000, 'Bob')
insert into dbo.[Employee] values (1, GETUTCDATE() - 5, 'Active', 3000, 'Az')
insert into dbo.[Employee] values (2, GETUTCDATE() - 30, 'Active', 1000, 'David')
insert into dbo.[Employee] values (2, GETUTCDATE() - 20, 'Inactive', 4000, 'Grace')
insert into dbo.[Employee] values (3, GETUTCDATE() - 2, 'Active', 4000, 'Grace')
insert into dbo.[Employee] values (3, GETUTCDATE() - 1, 'Active', 3000, 'Az')
insert into dbo.[Employee] values (4, GETUTCDATE() - 1, 'Active', 3000, 'Az')

select * from dbo.Employee

-- 2. basic usage
select * from dbo.Employee
for json path

select * from dbo.Employee
for json path, root ('abc')

select * from dbo.Employee
where id = 5
for json path

select * from dbo.Employee 
for json path, WITHOUT_ARRAY_WRAPPER

select ISJSON('[{"Id":1,"EmpId":1},{"Id":2,"EmpId":1}]')
select ISJSON('{"Id":1,"EmpId":1},{"Id":2,"EmpId":1}')

select empid,
	(select [CreatedAt], [Status] for json path) as StatusJson
from dbo.Employee

select id,
	(
		select [CreatedAt], [Status] 
			from dbo.Employee e1 
			where e1.id = e2.id
			for json path
	) as StatusJson
from dbo.Employee e2

select empid,
	(
		select [CreatedAt], [Status] 
			from dbo.Employee e1 
			where e1.empid = e2.empid
			for json path
	) as StatusJson
from dbo.Employee e2
group by empid

-- 3. demo: multiple json object to json array
drop table if exists #sourcedata

select empid,
	(select [CreatedAt], [Status] for json path, without_array_wrapper) as StatusJson
into #sourcedata
from dbo.Employee

select * from #sourcedata

-- 3.1. attempt failed
select empid,
		(
			select StatusJson
				from #sourcedata s2
				where s2.EmpId = s1.EmpId
				for json path, without_array_wrapper
		) as CombinedJson
from #sourcedata s1
group by empid

-- 3.2 attempt succeeded
select empid,
		(
			select 
				JSON_value(StatusJson, '$.CreatedAt') as [CreatedAt],
				JSON_value(StatusJson, '$.Status') as [Status]
				from #sourcedata s2
				where s2.EmpId = s1.EmpId
				for json path
		) as CombinedJson
from #sourcedata s1
group by empid