sql - SQL JSON_VALUE 选择父值

Question

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

1. 以逗号"properties"分隔的列中的子级。
2. 以逗号"title"分隔的列中的子级。

结果应该是

1. BoxTp, boxNo
   

2. 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"
        }
    }
}

标签： sqljsonsql-server

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