sql - 使用标量值和 JSON 对象作为 JSON 值

问题描述

我有一个本地表变量，我试图用 JSON 键值对填充它。有时值本身就是 JSON 字符串

DECLARE @Values TABLE
    (
        JsonKey varchar(200),
        JsonValue varchar(max)
    )

这最终看起来像的一个例子：

+---------+--------------------------------------+
| JsonKey |              JsonValue               |
+---------+--------------------------------------+
| foo     | bar                                  |
| foo     | [{"label":"fooBar","Id":"fooBarId"}] |
+---------+--------------------------------------+

填充后，我尝试将其全部构建为单个 JSON 字符串，如下所示：

DECLARE @Json JSON = 
    (
        SELECT  V.JsonKey as 'name',
                V.JsonValue as 'value'
        FROM    @Values V
        for json path
    )

问题在于它将 JSON 值转换为字符串，而不是将它们视为 JSON。这会导致这些值无法正确解析。

它最终看起来像的一个例子：

[
    {
        "name": "foo",
        "value": "bar"
    },
    {
        "name": "foo",
        "value": "[{\"label\":\"fooBar\",\"Id\":\"fooBarId\"}]"
    }
]

我试图让第二个 JSONvalue不会被转义或用双引号括起来。我想看到的是：

[
    {
        "name": "foo",
        "value": "bar"
    },
    {
        "key": "foo",
        "value": [
            {
                "label": "fooBar",
                "Id": "fooBarId"
            }
        ]
    }
]

如果该值只能是 JSON，我可以在 JSON 构建中使用 JSON_QUERY()，如下所示：

DECLARE @Json JSON = 
    (
        SELECT  V.JsonKey as 'name',
                JSON_QUERY(V.JsonValue) as 'value'
        FROM    @Values V
        for json path
    )

像这样构建它会给我想要的结果，但是当 JsonValue 列不是有效的 JSON 时会出错。我试图把它放在一个 case 语句中，只在 JsonValue 是有效 JSON 时使用 JSON_QUERY()，但是由于 case 语句需要始终输出相同的类型，所以它再次将它变成了一个字符串，我得到了重复第一个例子。我还没有找到一个优雅的解决方案，我真的觉得应该有一个我只是想念的。任何帮助将不胜感激

标签： sqlsql-server

一种可能的方法是生成具有重复列名的语句 ( JsonValue)。默认情况下FOR JSON AUTO，输出中不包含 NULL 值，因此结果是预期的 JSON。请注意，您不能INCLUDE_NULL_VALUES在语句中使用，否则最终的 JSON 将包含重复的键。

桌子：

DECLARE @Values TABLE (
   JsonKey varchar(200),
   JsonValue varchar(max)
)
INSERT INTO @Values 
   (JsonKey, JsonValue)
VALUES
   ('foo', 'bar'),
   ('foo', '[{"label":"fooBar","Id":"fooBarId"}]')

陈述：

SELECT
   JsonKey AS [name],
   JSON_QUERY(CASE WHEN ISJSON(JsonValue) = 1 THEN JSON_QUERY(JsonValue) END) AS [value],
   CASE WHEN ISJSON(JsonValue) = 0 THEN JsonValue END AS [value]
FROM @Values   
FOR JSON AUTO

结果：

[{"name":"foo","value":"bar"},{"name":"foo","value":[{"label":"fooBar","Id":"fooBarId"}]}]

sql - 使用标量值和 JSON 对象作为 JSON 值

问题描述

解决方案

推荐阅读