SQL Server 中 JSON 的使用

這裡記錄一下在 SQL Server 上，如何使用 JSON

假設我資料如下

id	name	properties
af76fdcc	Lorem ipsum	{ "example": "Lorem ipsum dolor sit amet, consectetur adipiscing elit.", "foo": [ { "id": 1, "bar": [{"value": "Suspendisse non libero aliquet, pharetra ipsum nec, volutpat lectus."}] }, { "id": 2, "bar": [{"value": "Nulla sed orci dictum, efficitur eros et, laoreet quam."}] } ] }
7deb4187	Sit amet	{ "foo": [ { "id": 1, "bar": [ {"value": "Etiam ut."}, {"value": "Lectus sit amet"}, {"value": "Maecenas vulputate urna ac"} ] } ] }
08167610	Proin mollis	{ "example": "Suspendisse quis libero et sapien mattis volutpat." }

如果我想找的 JSON 欄位是字串、數字、boolean等非物件的話，直接用 JSON_VALUE 即可，例如

select id, JSON_VALUE(properties, '$.example') as example_value from my_table

id	example_value
af76fdcc	Lorem ipsum dolor sit amet, consectetur adipiscing elit.
7deb4187
08167610	Suspendisse quis libero et sapien mattis volutpat.

如果我要指定的 array index ，也可以，例如

select id, JSON_VALUE(properties, '$.foo[0].id') as foo_0_id from my_table

id	foo_0_id
af76fdcc	1
7deb4187	1
08167610

展開 JSON Array

如果我要展開 JSON 裡的 array，就得用 CROSS APPLY OPENJSON

select id, foo_data.fid from my_table t
CROSS APPLY OPENJSON(t.properties, '$.foo')
WITH(
	[fid] int '$.id'
) foo_data

id	fid
af76fdcc	1
af76fdcc	2
7deb4187	1

如果要再展開底下的 JSON / Array，則：

select id, foo_data.fid, bar_data.bar from my_table t
CROSS APPLY OPENJSON(t.properties, '$.foo')
WITH(
	[fid] int '$.id',
	[fbar] nvarchar(max) '$.bar' as JSON 
) foo_data
CROSS APPLY OPENJSON(foo_data.fbar, '$')
WITH(
	[bar] nvarchar(max) '$.value' 
) bar_data

id	fid	bar
af76fdcc	1	Suspendisse non libero aliquet, pharetra ipsum nec, volutpat lectus.
af76fdcc	2	Nulla sed orci dictum, efficitur eros et, laoreet quam.
7deb4187	1	Etiam ut.
7deb4187	1	Lectus sit amet
7deb4187	1	Maecenas vulputate urna ac