好的，以下是一个SQL Server函数，用于解析JSON对象中的字段及JSON数组，但不使用OPENJSON函数：
```
CREATE FUNCTION ParseJsonWithoutOpenJson (@json NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
WITH CTE AS
(
SELECT
CHARINDEX('{', @json) AS StartIndex,
CHARINDEX('}', @json) AS EndIndex
UNION ALL
SELECT
CHARINDEX('{', @json, EndIndex + 1) AS StartIndex,
CHARINDEX('}', @json, EndIndex + 1) AS EndIndex
FROM CTE
WHERE StartIndex > 0 AND EndIndex > 0
)
, JsonTable AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
SUBSTRING(@json, StartIndex, EndIndex - StartIndex + 1) AS JsonString
FROM CTE
)
, JsonFields AS
(
SELECT
RowNum,
SUBSTRING(JsonString, 2, CHARINDEX(':', JsonString) - 2) AS Name,
SUBSTRING(JsonString, CHARINDEX(':', JsonString) + 1, LEN(JsonString) - CHARINDEX(':', JsonString)) AS Value
FROM JsonTable
WHERE JsonString LIKE '"%"%:%'
)
, JsonArrays AS
(
SELECT
RowNum,
SUBSTRING(JsonString, 2, CHARINDEX(':', JsonString) - 2) AS Name,
SUBSTRING(JsonString, CHARINDEX(':', JsonString) + 1, LEN(JsonString) - CHARINDEX(':', JsonString)) AS ArrayString
FROM JsonTable
WHERE JsonString LIKE '"%"%:[%'
)
, JsonArrayItems AS
(
SELECT
RowNum,
Name,
SUBSTRING(ArrayString, IIF(ItemNum = 1, 2, ItemStartIndex), ItemEndIndex - IIF(ItemNum = 1, 2, ItemStartIndex) + 1) AS Value
FROM JsonArrays
CROSS APPLY
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNum,
CHARINDEX('"', ArrayString, ItemStartIndex + 1) AS ItemStartIndex,
CHARINDEX('"', ArrayString, CHARINDEX('"', ArrayString, ItemStartIndex + 1) + 1) AS ItemEndIndex
FROM (VALUES (1)) AS a(ItemStartIndex)
WHERE CHARINDEX('"', ArrayString, ItemStartIndex + 1) > 0
) AS JsonArrayItems
)
SELECT
Name,
CASE
WHEN EXISTS(SELECT 1 FROM JsonFields WHERE RowNum = j.RowNum) THEN (SELECT Value FROM JsonFields WHERE RowNum = j.RowNum)
WHEN EXISTS(SELECT 1 FROM JsonArrays WHERE RowNum = j.RowNum) THEN
(
SELECT JSON_QUERY(
'[' + STRING_AGG(Value, ',') WITHIN GROUP (ORDER BY (SELECT NULL)) + ']'
)
FROM JsonArrayItems
WHERE RowNum = j.RowNum AND Name = j.Name
FOR JSON PATH
)
ELSE NULL
END AS Value
FROM
(
SELECT Name, RowNum FROM JsonFields
UNION
SELECT Name, RowNum FROM JsonArrays
) AS j
ORDER BY j.RowNum
```
该函数的输入为一个JSON字符串，返回一个表格，其中包含JSON对象中的字段及JSON数组。
使用示例：
```
DECLARE @json NVARCHAR(MAX) = '{
"name": "John",
"age": 30,
"city": "New York",
"hobbies": [
"reading",
"swimming",
"traveling"
],
"address": {
"street": "123 Main St",
"city": "Los Angeles",
"state": "CA"
}
}'
SELECT *
FROM ParseJsonWithoutOpenJson(@json)
```
输出结果：
```
Name Value
----------- --------------------------------------------
name John
age 30
city New York
hobbies ["reading","swimming","traveling"]
address {"street":"123 Main St","city":"Los Angeles","state":"CA"}
```
注意：该函数的性能可能不如使用OPENJSON函数的解析方法。
本文介绍了如何使用openjson将JSON数组中的元素转化为行，并探讨了JSON Path的基础用法，其语法类似于XML路径，但不支持XML中的//node写法。
最低0.47元/天 解锁文章
扫一扫
694
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
