SQL Server中$[*]类JSONPath的等效实现方法？

问题背景 #

你需要对以下JSON数据提取所有name字段值，组成目标JSON数组：

[
    {"id": 1, "name": "John"}, 
    {"id": 2, "name": "Mary"}, 
    {"id": 3, "name": "Peter"}
]

预期结果：

["John", "Mary", "Peter"]

但执行以下SQL语句时触发错误：

SELECT JSON_VALUE(json_data, '$[*].name')
FROM users

错误提示：

JSON path is not properly formatted. Unexpected character '*' is found at position 2.

原因说明 #

SQL Server的JSON_VALUE函数仅支持返回单个标量值，不支持$[*]这类通配符JSONPath语法，因此无法直接通过它提取数组中所有元素的指定字段。

可行解决方案 #

方法一：拆分解析后聚合生成JSON数组 #

通过OPENJSON将JSON数组拆分为行数据，提取每个元素的name值，再拼接成合法的JSON数组：

SELECT 
  CONCAT('[', STRING_AGG('"' + STRING_ESCAPE(j.name, 'json') + '"', ','), ']') AS names_array
FROM users
CROSS APPLY OPENJSON(users.json_data)
WITH (
  name NVARCHAR(100) '$.name'
) AS j
GROUP BY users.json_data;

方法二：返回JSON类型结果 #

如果需要让结果被SQL Server识别为JSON类型，可结合JSON_QUERY使用：

SELECT 
  JSON_QUERY(CONCAT('[', STRING_AGG('"' + STRING_ESCAPE(j.name, 'json') + '"', ','), ']')) AS names_array
FROM users
CROSS APPLY OPENJSON(users.json_data)
WITH (
  name NVARCHAR(100) '$.name'
) AS j
GROUP BY users.json_data;

关键细节说明 #

• OPENJSON：负责将JSON数组转换为关系型行集，通过WITH子句映射出需要提取的name字段。
• STRING_ESCAPE：处理name值中的特殊字符（如双引号、反斜杠），避免生成无效JSON。
• STRING_AGG：将多个name值拼接为逗号分隔的字符串，再通过CONCAT包裹成JSON数组格式。
• JSON_QUERY：确保生成的字符串被识别为JSON类型，而非普通字符串。

内容的提问来源于stack exchange，提问作者celsowm