Subquery returned more than 1 value while running OPENJSON JSON_QUERY

Question

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

The error is telling you the problem here. What about the error don't you understand and we can try to elaborate. — Thom A
– Thom A ♦, Commented Aug 6, 2020 at 16:55

Zhorov · Accepted Answer · 2020-08-07 07:32:53Z

2

The reason for this error is the JSON_QUERY((SELECT content FROM employees), '$.skills') part of the statement, because JSON_QUERY() expects a JSON string. To solve this error you may try the following statement:

SELECT e.id, j1.name, j2.language, j2.years
FROM employees e
OUTER APPLY OPENJSON(e.content) WITH (
   name nvarchar(100) '$.name',
   skills nvarchar(max) '$.skills' AS JSON
) j1
OUTER APPLY OPENJSON(j1.skills) WITH (
   language nvarchar(100) '$.language',
   years int '$.years'
) j2

Result:

id  name        language    years
1   John Doe    SQL         3
1   John Doe    C#          4
2   Alice Smith JS          5

edited Aug 7, 2020 at 7:32

answered Aug 6, 2020 at 19:23

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Comment

pizycki Over a year ago

And here is example on MS docs learn.microsoft.com/en-us/sql/relational-databases/json/…

Collectives™ on Stack Overflow

Subquery returned more than 1 value while running OPENJSON JSON_QUERY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related