根据这个答案,我发现了一个问题。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 }'
1条答案
按热度按时间vof42yt11#
你需要使用
AS JSON
选项来指定$.some_object
属性包含内部json对象: