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')

Answer 1

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.)