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.

CodePudding user response：

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