Question 108970 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server 2019 and path for JSON_QUERY

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
    }
Tags:

2

Answers


  1. 0

    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

    enter image description here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search