Can't get access to data from nested json's array

Question

How to retrieve values from employment_types (type, salary) and skills (name, level) arrays and show them in columns? I tried with employment_types and it doesn't work not to mention skills:

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 *
FROM OPENJSON(@JSON, '$.employment_types')
WITH
(
    type nvarchar(50) '$.type',
    salary varchar(max) '$.salary'

)

There are almost 7000 records and I'd like to show mentioned above columns from all of them.

Answer 1

It's hard to know exactly what you want, given that both employment_types and skills are arrays. But assuming employment_types always has only one element, you could do something like this

SELECT
  j1.title,
  j1.experience_level,
  j1.employment_type,
  salary = j1.salary_currency + ' ' + CONCAT(j1.salary_from, ' - ', j1.salary_to),
  j2.name,
  j2.level
FROM OPENJSON(@JSON)
  WITH (
    title nvarchar(100),
    experience_level nvarchar(10),
    employment_type nvarchar(50) '$.employment_types[0].type',
    salary_from int '$.employment_types[0].salary.from',
    salary_to int '$.employment_types[0].salary.to',
    salary_currency char(3) '$.employment_types[0].salary.currency',
    skills nvarchar(max) AS JSON
  ) j1
CROSS APPLY OPENJSON(j1.skills)
WITH
(
    name nvarchar(50),
    level int
) j2

db<>fiddle

• Since we are pulling data directly from the root object, we don't need a JSON path argument. OPENJSON will automatically break out an array into separate rows. If you just wanted employment_types, you could go directly to that with a path argument.
• employment_types[0] means to only get the first element of the array. If you want all the elements, you will need another OPENJSON
• Note the use of AS JSON for skills, this means that the entire JSON array is pulled out, and can then be pushed through another call to OPENJSON