SQL JSON_VALUE 选择父值

Question

我有这个 JSON，我需要提取 2 个字符串

"properties" 的 child 在以逗号分隔的列中。

"title" 的 child 在以逗号分隔的列中。

结果应该是

BoxTp, boxNo

Box Type, Box Number

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "properties": {
        "BoxTp": {
            "title": "Box Type",
            "type": [
                "null",
                "string"
            ],
            "description": "Type"
        },
        "boxNo": {
            "title": "Box Number",
            "type": [
                "integer",
                "null"
            ],
            "description": "Box No.",
            "format": "int32"
        }
    }
}

Answer 1

您只需要解析输入 JSON 并连接结果。当然，您需要 SQL Server 2016+ 才能使用内置的 JSON 支持和 SQL Server 2017+ 使用 STRING_AGG() 进行字符串聚合。 :

DECLARE @json nvarchar(max) = N'{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "properties": {
        "BoxTp": {
            "title": "Box Type",
            "type": [
                "null",
                "string"
            ],
            "description": "Type"
        },
        "boxNo": {
            "title": "Box Number",
            "type": [
                "integer",
                "null"
            ],
            "description": "Box No.",
            "format": "int32"
        }
    }
}'

SELECT STRING_AGG([key], ', ') AS ChildsOfProperties
FROM OPENJSON(@json, '$.properties')

SELECT STRING_AGG(j2.title, ', ') AS ChildsOfTitles
FROM OPENJSON(@json, '$.properties') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (title nvarchar(100) '$.title') j2

结果:

ChildsOfProperties
-----------------
BoxTp, boxNo

ChildsOfTitles
--------------------
Box Type, Box Number

如果您需要单个语句:

SELECT 
   STRING_AGG(j1.[key], ', ') AS ChildSOfProperties,
   STRING_AGG(j2.title, ', ') AS ChildSOfTitles
FROM OPENJSON(@json, '$.properties') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (title nvarchar(100) '$.title') j2