使用SQL Server如何查询JSON格式列中的元素并提取员工字段数据

前置说明 #

你提供的employee info字段内容存在两处JSON语法问题，需要先修正才可被SQL Server的JSON解析函数识别：

• 数组第二个元素前多了一个多余的{
• JSON属性和值使用了单引号，SQL Server要求使用双引号
  如果上述问题是你输入时的手误，实际存储的是合法JSON，可以对应删减下面SQL里的字符替换逻辑。

具体查询语句 #

假设你的表名为manager_emp_map，可直接使用如下查询：

SELECT
    [manager id],
    emp_name,
    emp_id
FROM
    manager_emp_map -- 替换为你实际的表名
CROSS APPLY OPENJSON(
    -- 修正非法JSON格式，如实际存储为合法JSON可调整替换逻辑
    REPLACE(REPLACE([employee info], '{{', '{'), '''', '"')
)
WITH (
    emp_name VARCHAR(50) '$.emp_name',
    emp_id VARCHAR(20) '$.emp_id'
)

逻辑说明 #

• REPLACE函数负责把原始字段的非法字符替换为符合JSON规范的格式
• OPENJSON是SQL Server 2016及以上版本支持的JSON解析函数，可将JSON数组拆分为独立的行
• CROSS APPLY操作符将拆解后的员工行和对应的manager id关联，实现一行拆多行的效果
• WITH子句直接从解析后的JSON对象中提取你需要的emp_name和emp_id字段

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