Reading JSON array containing JSON object into rows

Question

Based from this answer I found one problem. JSON object is return as NULL.

Suppose that I have a JSON like this:

{
    "array_in_json": [
        { "number": 1, "character": "A", "some_object": { "code": 65 } },
        { "number": 2, "character": "B", "some_object": { "code": 66 } },
        { "number": 3, "character": "C", "some_object": { "code": 67 } },
        { "number": 4, "character": "D", "some_object": { "code": 68 } }
    ]
}

With a query like this:

DECLARE @json NVARCHAR(MAX)
SET @json = '{
    "array_in_json": [
        { "number": 1, "character": "A", "some_object": { "code": 65 } },
        { "number": 2, "character": "B", "some_object": { "code": 66 } },
        { "number": 3, "character": "C", "some_object": { "code": 67 } },
        { "number": 4, "character": "D", "some_object": { "code": 68 } }
    ]
}'

SELECT
    a.[number],
    a.[character],
    a.[some_object]
FROM
    OPENJSON(@json) WITH (
        Actions NVARCHAR(MAX) '$.array_in_json' AS JSON
    ) AS i
    CROSS APPLY (
        SELECT * FROM
            OPENJSON(i.Actions) WITH (
                [number] NVARCHAR(MAX) '$.number',
                [character] NVARCHAR(MAX) '$.character',
                [some_object] NVARCHAR(MAX) '$.some_object'
            )
    ) a

This's the result:

    number  |   character   |   some_object
-------------------------------------------------
    1       |       'A'     |   NULL
    2       |       'B'     |   NULL
    3       |       'C'     |   NULL
    4       |       'D'     |   NULL

I want to know is there a way to get a result like this? (Return JSON as escaped string instead of NULL)

    number  |   character   |   some_object
-------------------------------------------------
    1       |       'A'     |   '{ "code": 65 }'
    2       |       'B'     |   '{ "code": 66 }'
    3       |       'C'     |   '{ "code": 67 }'
    4       |       'D'     |   '{ "code": 68 }'

You need AS JSON in the explicit schema: ... [some_object] NVARCHAR(MAX) '$.some_object' AS JSON — Zhorov
– Zhorov, Commented Jun 15, 2020 at 8:53

Zhorov · Accepted Answer · 2020-06-15 08:56:14Z

2

You need to use AS JSON option in your schema definition to specify that the $.some_object property contains an inner JSON object:

SELECT
    a.[number],
    a.[character],
    a.[some_object]
FROM
    OPENJSON(@json) WITH (
        Actions NVARCHAR(MAX) '$.array_in_json' AS JSON
    ) AS i
    CROSS APPLY (
        SELECT * FROM
            OPENJSON(i.Actions) WITH (
                [number] NVARCHAR(MAX) '$.number',
                [character] NVARCHAR(MAX) '$.character',
                [some_object] NVARCHAR(MAX) '$.some_object' AS JSON
            )
    ) a

answered Jun 15, 2020 at 8:56

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Comment

TacticalBacon Over a year ago

Thanks! I thought AS JSON is column alias.

Collectives™ on Stack Overflow

Reading JSON array containing JSON object into rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related