sql—将包含json对象的json数组读入行

idfiyjo8  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(429)

根据这个答案,我发现了一个问题。json对象返回为 NULL .
假设我有这样一个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 } }
    ]
}

对于这样的查询:

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

结果如下:

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

我想知道有没有办法得到这样的结果(返回json作为转义字符串,而不是 NULL )

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

vof42yt11#

你需要使用 AS JSON 选项来指定 $.some_object 属性包含内部json对象:

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

相关问题