I'm trying to write a query against following table
create table employees (id int identity(1,1) primary key, content nvarchar(max));
insert into employees (content)
values (
N'{
"name": "John Doe",
"skills": [
{
"language": "SQL",
"years": 3
},
{
"language": "C#",
"years": 4
}
]
}'),
(
N'{
"name": "Alice Smith",
"skills": [
{
"language": "JS",
"years": 5
}
]
}
')
;
What I'm up to is to end up with the following result
+ id + name + language + years +
| 1 | John Doe | SQL | 3 |
| 2 | John Doe | C# | 4 |
| 3 | Alice Smith | JS | 3 |
Here is what I came up with
SELECT * FROM
OPENJSON(JSON_QUERY((SELECT content FROM employees), '$.skills'))
WITH (
Language VARCHAR(50) '$.language',
Years int '$.years'
)
You can check it out in dbfiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8ed626ed8441ea6ccc62b8feec99c227
It works when content
column contains a single row, but fails when there is more.
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I understand what it means, yet I cannot figure out how to get to it in different way.
I've been trying with OPENJSON and JSON_QUERY