最近在项目中需要在 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 数据的你有所帮助!