使用深度嵌套数组循环的SQL查询OpenJson

OPENJSON ( expression [ , path ] )

DECLARE @json NVARCHAR(MAX) = '
{
  "employees": [
    {
      "id": 1,
      "name": "John",
      "skills": [
        {
          "name": "HTML",
          "level": "Intermediate"
        },
        {
          "name": "CSS",
          "level": "Advanced"
        }
      ]
    },
    {
      "id": 2,
      "name": "Jane",
      "skills": [
        {
          "name": "JavaScript",
          "level": "Advanced"
        },
        {
          "name": "SQL",
          "level": "Intermediate"
        }
      ]
    }
  ]
}'

SELECT emp.id, emp.name, skill.name, skill.level
FROM OPENJSON(@json, '$.employees') 
WITH (
  id INT '$.id',
  name NVARCHAR(50) '$.name',
  skills NVARCHAR(MAX) '$.skills' AS JSON
) AS emp
CROSS APPLY OPENJSON(emp.skills)
WITH (
  name NVARCHAR(50) '$.name',
  level NVARCHAR(50) '$.level'
) AS skill

id  name  skill       level
----------------------------
1   John  HTML        Intermediate
1   John  CSS         Advanced
2   Jane  JavaScript  Advanced
2   Jane  SQL         Intermediate