SQL Server Openjson to fetch details into a table

Question

I have the following JSON and expecting a table out of it.

{
  "Response": [
    {
      "questionId": 1,
      "answer": [
        "5","6"
      ]
    },
    {
      "questionId": 2,
      "answer": [
        "5"
      ]
    }
  ]
}

And the expected table...

I tried with the following, but didn't the exact output that i'm looking for.

SELECT QuestionId,Answer FROM OPENJSON(@responseJson, N'$.Response') r
 CROSS APPLY OPENJSON (r.value) WITH ( QuestionId   varchar(200)   '$.questionId', Answer nvarchar(max) '$.answer')

Welcome to SO. When posting a **question**, people expect you to ask a question (surprise! :) ). What is yours? What have you tried already to answer your question? — Thom A, May 09 '18 at 10:42
possible duplicate of : https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json — Udit Solanki, May 09 '18 at 11:05

Jeroen Mostert · Answer 1 · 2018-05-09T11:00:42.733

Getting JSON into relational data is like peeling an onion. Use OPENJSON and CROSS APPLY until you've got all the layers you want. Use WITH to simplify the task of mapping values along the way.

SELECT questions.questionId, answers.answerId AS answer
FROM OPENJSON(@responseJson, '$.Response')
WITH (
    questionId INT,
    answer NVARCHAR(MAX) AS JSON
) AS questions
CROSS APPLY OPENJSON(answer)
WITH (
    answerId INT '$'
) AS answers;

There are shorter ways of writing this query, but I'd argue they're not as clear. (And if the execution plans are any indication, being explicit about the structure of the JSON using WITH helps with estimating memory requirements.)

SQL Server Openjson to fetch details into a table

1 Answers1