sql - 使用 OPENJSON() 返回对象的所有值

Question

这是我的问题:我的 JSON 可能会根据 API 响应而有所不同，并且不能保证下次有发布请求时“shortTitle”可以出现在我的 JSON 中。我必须弄清楚如何通过 JSON“循环”(如果这是正确的术语)来获取所有 fieldID 和内容而不丢失任何数据。

这是查询:

DECLARE
// hardcoded json to simulate a post request
@json nvarchar(max) = '{
    "itemTypeID": 13,
    "authorRights": {
        "fieldID": 15,
        "content": "abcd"
    },
    "language": {
        "fieldID": 7,
        "content": "abcd"
    },
    "url": {
        "fieldID": 13,
        "content": "abcd"
    },
    "shortTitle": {
        "fieldID": 8,
        "content": "abcd"
    },
    "data": {
        "fieldID": 6,
        "content": "2022-03-02"
    },
    "summary": {
        "fieldID": 2,
        "content": "abcd"
    },
    "subject": {
        "fieldID": 60,
        "content": "abcd"
    }
}';

BEGIN

SELECT* FROM OPENJSON(@json)
WITH(
fieldID bigint '$.data.fieldID',
content nvarchar(255) '$.data.content'
)

END;

此查询仅返回“数据”( results ) 的 fieldID 和内容。如何返回所有对象的 fieldID 和内容？

Answer 1

如果我正确理解输入 JSON 的结构，则您只需要解析嵌套的 JSON 对象。因此，使用带有默认架构的 OPENJSON() 和附加的 APPLY 运算符可能是解决您问题的方法。正如 documentation 中所解释的那样，具有默认架构的 OPENJSON() 函数返回一个包含列 key、value 和 type 的表，并且type 列保存一个 int 值，其中包含返回值的类型。

SELECT j1.[key], j2.fieldID
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON (j1.[value]) WITH (fieldID bigint '$.fieldID') j2
WHERE j1.[type] = 5

结果:

key          fieldID
---------------------
authorRights 15
language     7
url          13
shortTitle   8
data         6
summary      2
subject      60