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

Answer 1

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