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.

What is your expected result for this sample, given that there may be multiple employment_types and multiple skills? — Charlieface
– Charlieface, Commented Oct 3, 2021 at 0:27

Charlieface · Accepted Answer · 2021-10-03 15:48:50Z

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

edited Oct 3, 2021 at 15:48

answered Oct 3, 2021 at 0:40

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

beginsql Over a year ago

Thanks, I really appreciate your effort but I have one more question though. Before I asked my question here I looked through several similar questions/tutorials and in vast majority of them they are using JSONpath parameter in OPENJSON like I did in my code (OPENJSON(@JSON, '$.employment_types')). I'm curious why it doesn't work and how this zero in brackets in your code works. Just need further explanation about it.

Charlieface Over a year ago

OK given you a bit more explanation.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related