SQL Server如何从嵌套JSON数组中提取employment_types、skills字段展示为列

问题原因 #

你之前的查询失效是因为传入的JSON根节点为数组，employment_types属于根数组下每个子对象的属性，并非直接挂载在根节点下，所以路径$.employment_types无法匹配到对应数据。

解决方案 #

1. 基础展开查询（每条技能对应一行数据）

适合需要对单条技能做后续计算、过滤的场景：

declare @json nvarchar(max)
set @json = '[
  {
    "title": "IT Admin",
    "experience_level": "mid",
    "employment_types": [
      {
        "type": "permanent",
        "salary": null
      }
    ],
    "skills": [
      {
        "name": "Security",
        "level": 3
      },
      {
        "name": "WIFI",
        "level": 3
      },
      {
        "name": "switching",
        "level": 3
      }
    ]
  },
  {
    "title": "Lead QA Engineer",
    "experience_level": "mid",
    "employment_types": [
      {
        "type": "permanent",
        "salary": {
          "from": 7000,
          "to": 13000,
          "currency": "pln"
        }
      }
    ],
    "skills": [
      {
        "name": "Embedded C",
        "level": 4
      },
      {
        "name": "Quality Assurance",
        "level": 4
      },
      {
        "name": "C++",
        "level": 4
      }
    ]
  }
]';

SELECT 
    j.title,
    j.experience_level,
    et.type AS employment_type,
    JSON_VALUE(et.salary_raw, '$.from') AS salary_from,
    JSON_VALUE(et.salary_raw, '$.to') AS salary_to,
    JSON_VALUE(et.salary_raw, '$.currency') AS salary_currency,
    s.name AS skill_name,
    s.level AS skill_level
FROM OPENJSON(@json) -- 第一步：展开根数组获取基础职位信息
WITH (
    title nvarchar(100) '$.title',
    experience_level nvarchar(50) '$.experience_level',
    employment_types nvarchar(max) '$.employment_types' AS JSON, -- 标记为JSON类型供后续展开
    skills nvarchar(max) '$.skills' AS JSON
) AS j
-- 第二步：展开每个职位下的employment_types数组
CROSS APPLY OPENJSON(j.employment_types)
WITH (
    type nvarchar(50) '$.type',
    salary_raw nvarchar(max) '$.salary' AS JSON
) AS et
-- 第三步：展开每个职位下的skills数组
CROSS APPLY OPENJSON(j.skills)
WITH (
    name nvarchar(100) '$.name',
    level int '$.level'
) AS s

2. 聚合查询（每个职位对应一行数据）

适合直接展示的场景，把同一个职位的所有技能合并为单个字段：

SELECT 
    j.title,
    j.experience_level,
    et.type AS employment_type,
    JSON_VALUE(et.salary_raw, '$.from') AS salary_from,
    JSON_VALUE(et.salary_raw, '$.to') AS salary_to,
    JSON_VALUE(et.salary_raw, '$.currency') AS salary_currency,
    STRING_AGG(CONCAT(s.name, '(等级', s.level, ')'), ', ') AS skills
FROM OPENJSON(@json)
WITH (
    title nvarchar(100) '$.title',
    experience_level nvarchar(50) '$.experience_level',
    employment_types nvarchar(max) '$.employment_types' AS JSON,
    skills nvarchar(max) '$.skills' AS JSON
) AS j
CROSS APPLY OPENJSON(j.employment_types)
WITH (
    type nvarchar(50) '$.type',
    salary_raw nvarchar(max) '$.salary' AS JSON
) AS et
CROSS APPLY OPENJSON(j.skills)
WITH (
    name nvarchar(100) '$.name',
    level int '$.level'
) AS s
GROUP BY j.title, j.experience_level, et.type, JSON_VALUE(et.salary_raw, '$.from'), JSON_VALUE(et.salary_raw, '$.to'), JSON_VALUE(et.salary_raw, '$.currency')

注意事项 #

• 如果JSON是存储在表字段中，把@json替换为对应字段名、加上FROM 你的表名即可，7000条数据量下查询性能无压力
• 如果存在employment_types/skills为空数组的职位，把CROSS APPLY替换为OUTER APPLY即可保留这些职位记录，不会被过滤

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