SQL Server 中处理 JSON 数据

最近在项目中需要在 SQL Server 中处理 JSON 数据，于是学习了解了一下，在这里记录下：）

背景

项目中需要存储和查询半结构化的数据，而 JSON 数据是个理想的选择。然而，我对 SQL Server 中的 JSON 处理并不是很熟悉，于是开始了解这个领域。

发现1：JSON 函数的强大应用

SQL Server 2016 引入了一系列内置的 JSON 函数，如 ISJSON、JSON_VALUE、JSON_QUERY 和 JSON_MODIFY。这些函数在 NVARCHAR 列中存储 JSON 数据的查询和解析方面提供了便利。

-- 示例：从 JSON 文本中提取值
SELECT Name, Surname, 
JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode, 
JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' + 
JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address, 
JSON_QUERY(jsonCol, '$.info.skills') AS Skills 
FROM People 
WHERE ISJSON(jsonCol) > 0 
AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade' 
AND STATUS = 'Active' 
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

发现2：JSON 数据的灵活存储

JSON 数据在 NVARCHAR 列中的存储方式依然非常灵活。通过 OPENJSON 函数，可以将 JSON 数据转换为行集，为我们提供了更多处理的空间。

-- 示例：将 JSON 数据转换为行集 
DECLARE @json NVARCHAR(MAX); 

SET @json = N'[ {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"} ]'; 

SELECT * FROM OPENJSON(@json) 
WITH ( id INT 'strict $.id', firstName NVARCHAR(50) 
'$.info.name', 
lastName NVARCHAR(50) 
'$.info.surname', 
age INT, 
dateOfBirth DATETIME2 '$.dob' );

结论

问题解决！同时了解到在 SQL Server 中处理 JSON 数据的一些建议和技巧。尽管 SQL Server 2019 没有我期望的原生 JSON 数据类型，但现有的函数和存储方式足够应对各种场景。

希望我的分享对正在探索 SQL Server 中处理 JSON 数据的你有所帮助！