SQL Server 2019 and path for JSON_QUERY

Question

I'm trying to extract data from Json stored in a column and save as relational data in tables. The Json represents submissions of a pre-start checklist by a driver.

The input data is stored in a table, with SubmitId being the primary key and FormDetails holding the json. I get the unprocessed submissions and attempt to process them. To make life easier, I add the submitId key to the unprocessed json with JSON_MODIFY.

Doing it with a WHILE loop because that's how my brain works. I get the json into a variable, and then run some OPENJSON(JSON_QUERY(@json, <path>)) on it

I can successfully extract the FormSubmission and the SectionSubmission details.

SELECT 
    DeviceId, FormId, Id, IsPassed, SubmitDate, SubmitId, UserId
FROM
    OPENJSON(@json) 
    WITH (
        SubmitId int '$.submitId',
        DeviceId varchar(500) '$.deviceId',
        FormId int '$.formId',
        Id int '$.id',
        IsPassed bit '$.isPassed',
        SubmitDate datetime '$.submitDate',
        --SubmittedSections varchar(max) '$.submittedSections',
        UserId int '$.userId'
        ) AS form

SELECT 
    @SubmitId AS SubmitId,
    SectionId,
    FormId,
    UserId,
    CASE 
        WHEN UPPER(SectionDisplayed) = 'TRUE' THEN 1
        ELSE 0
    END AS SectionDisplayed,
    SectionPass,
    SectionTotal
FROM 
    OPENJSON(JSON_QUERY(@json, '$.submittedSections')) 
    WITH (
        --Answers varchar(max) '$.answers',
        FormId int '$.formId',
        Id int '$.id',
        SectionDisplayed varchar(5) '$.sectionDisplayed',
        SectionId int '$.sectionId',
        SectionPass int '$.sectionPass',
        SectionTotal int '$.sectionTotal',
        UserId int '$.userId'
    ) AS section

However, when trying the same for the answers, I get a header row with the column names, and no data

    SELECT
        @SubmitId as SubmitId,
        SectionId,
        QuestionId,
        CASE 
            WHEN UPPER(AnswerBoolean) = 'TRUE' THEN 1
            ELSE 0
        END as AnswerBoolean,
        Comment,
        FailType,
        UserId
    FROM OPENJSON(JSON_QUERY(@json, '$.submittedSections.answers')) 
    WITH (
        SectionId int '$.sectionId',
        QuestionId int '$.questionId',
        AnswerBoolean varchar(5) '$.answerBoolean',
        Comment varchar(max) '$.comment',
        FailType int '$.failType',
        UserId int '$.userId'
        --Id int '$.id', -- Always 0
    ) 
    as answer

When removing the schema stuff to see what is coming back

    SELECT
        @SubmitId as SubmitId,
        *
    FROM OPENJSON(JSON_QUERY(@json, '$.submittedSections.answers')) 
    as answer

I get the following results header with no data (not even the SubmitId)

SubmitId	key	value	type

I initially tried to do this using CROSS APPLY following the example in question 58172902 but couldn't get it going, hence the steps approach.

Actual Question

I'm assuming I've made a mistake in the path for my JSON_QUERY. What should it be? Or what else is wrong?

I'm expecting results that are a table of submitted answers.

The json, prettified:

    {
        "deviceId": "8da23f818430c282",
        "formId": 2,
        "id": 0,
        "isPassed": true,
        "submitDate": "2023-03-27T15:40:59",
        "submittedSections": [
            {
                "answers": [
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 1,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 2,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 3,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 4,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 5,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 6,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 7,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 8,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 9,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 10,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 11,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 12,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 13,
                        "sectionId": 1,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 14,
                        "sectionId": 1,
                        "userId": 0
                    }
                ],
                "formId": 2,
                "id": 0,
                "sectionDisplayed": true,
                "sectionId": 1,
                "sectionPass": 14,
                "sectionTotal": 14,
                "userId": 0
            },
            {
                "answers": [
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 1,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 2,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 3,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 4,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 5,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 2,
                        "id": 0,
                        "questionId": 6,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 7,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 8,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 9,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 10,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 11,
                        "sectionId": 2,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 12,
                        "sectionId": 2,
                        "userId": 0
                    }
                ],
                "formId": 2,
                "id": 0,
                "sectionDisplayed": true,
                "sectionId": 2,
                "sectionPass": 12,
                "sectionTotal": 12,
                "userId": 0
            },
            {
                "answers": [
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 1,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 2,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 3,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 4,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 5,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 6,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 7,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 8,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 9,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 0,
                        "id": 0,
                        "questionId": 10,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 11,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 12,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 13,
                        "sectionId": 3,
                        "userId": 0
                    },
                    {
                        "answerBoolean": true,
                        "comment": "",
                        "failType": 1,
                        "id": 0,
                        "questionId": 14,
                        "sectionId": 3,
                        "userId": 0
                    }
                ],
                "formId": 2,
                "id": 0,
                "sectionDisplayed": true,
                "sectionId": 3,
                "sectionPass": 14,
                "sectionTotal": 14,
                "userId": 0
            }
        ],
        "userId": 0
    }

score 1 · Accepted Answer · answered Mar 29 '23 at 11:36

You don't need JSON_QUERY as OPENJSON can accept a path. But you can only break out the lowest-level array, you cannot break out multiple array levels this way, unless you use a fixed index such as $.submittedSections[0].answers.

So just use CROSS APPLY to feed one OPENJSON into the next. Note that you don't need WHILE loops either, you can APPLY the original OPENJSON directly from the main table.

Also you can use bit as a data type and it will convert from true or false.

SELECT
  d.SubmitId,
  SectionId,
  QuestionId,
  AnswerBoolean,
  Comment,
  FailType,
  UserId
FROM YourData d
CROSS APPLY OPENJSON(d.json, '$.submittedSections')
  WITH (
    answers nvarchar(max) AS JSON
  ) AS sections
CROSS APPLY OPENJSON(sections.answers)
    WITH (
        SectionId int '$.sectionId',
        QuestionId int '$.questionId',
        AnswerBoolean bit '$.answerBoolean',
        Comment varchar(max) '$.comment',
        FailType int '$.failType',
        UserId int '$.userId'
        --Id int '$.id', -- Always 0
    ) 
    as answer;

db<>fiddle

You could also add more OPENJSON calls if you want details from higher levels.

Accepted as this felt a bit more complete. – Hecatonchires Mar 30 '23 at 02:47 — Hecatonchires, Mar 30 '23 at 02:47

score 0 · Answer 2 · answered Mar 29 '23 at 05:27

I have almost done it. Just need to add the answers array as JSON in your first OPENJSON statement. Then perform the cross apply:

DECLARE @SubmitId INT = 1

SELECT 
    @SubmitId AS SubmitId,
    section.SectionId,
    section.FormId,
    section.UserId,
    CASE 
        WHEN UPPER(section.SectionDisplayed) = 'TRUE' THEN 1
        ELSE 0
    END AS SectionDisplayed,
    section.SectionPass,
    section.SectionTotal,
    answers_data.*
FROM 
    OPENJSON(JSON_QUERY(@json, '$.submittedSections')) 
    WITH (
        --Answers varchar(max) '$.answers',
        FormId int '$.formId',
        Id int '$.id',
        SectionDisplayed varchar(5) '$.sectionDisplayed',
        SectionId int '$.sectionId',
        SectionPass int '$.sectionPass',
        SectionTotal int '$.sectionTotal',
        UserId int '$.userId',
        answers nvarchar(MAX) '$.answers' AS JSON
    ) AS section
CROSS APPLY OPENJSON(section.answers)
WITH (
        AnswerBoolean varchar(5) '$.answerBoolean',
        Comment varchar(max) '$.comment',
        questionID INT '$.questionId',
        FailType int '$.failType',
        UserId int '$.userId'
        --Id int '$.id', -- Always 0
    ) answers_data

So frustrating! Why doesn't my submitted answer part work? $.submittedSections.answers should be equivalent to $.submittedSections (as section) => section.answers. On my way home now, will test and confirm answer tomoz :) — Hecatonchires, Mar 29 '23 at 06:02

SQL Server 2019 and path for JSON_QUERY

2 Answers2