将两列转换为键值对的JSON对象？

Question

使用 FOR JSON AUTO 或者 FOR JSON PATH 在下面的记录集上（代表一个产品的属性）：

attribute | value
-----------------
color     | red
size      | small

将会产生：

[{"attribute":"color","value":"red"},{"attribute":"size","value":"small"}]

有没有办法生产以下结果：

{"color":"red","size":"small"}

注意，每个产品属性都不同，因此每个产品的记录集也不同。PIVOT不是一个选项，因为它需要动态SQL！似乎我们需要一个函数来能够与产品表进行交叉，例如生成一个产品目录。

- dNitro

Answer 1

我在以下脚本中使用 SQL Server 2017 中的字符串拼接函数 string_agg，而不是 SQL Server 2016 中的 JSON 函数。详见此处

/*create table ProductAttributes (
    product int,
    attribute varchar(40),
    value varchar(40)
)
insert into ProductAttributes select 1, 'color', 'red'
insert into ProductAttributes select 1, 'size', 'small'
insert into ProductAttributes select 2, 'processor', 'intel'
insert into ProductAttributes select 2, 'ram', '16'
insert into ProductAttributes select 2, 'weight', '2'*/

select 
    product, '{' + STRING_AGG( '"' + attribute + '":"' + STRING_ESCAPE(value,'json') + '"' ,',') + '}' as attributes
from ProductAttributes 
group by product

以下是这两个产品条目的输出结果 产品属性 1 {"color":"红色","size":"小号"} 2 {"processor":"英特尔","ram":"16","weight":"2"}

如果您使用的是 SQL Server 2017 之前的版本，您可以使用SQL XML Path 字符串连接，如下所示。

SELECT
    product,
  '{' + STUFF(
    (
    SELECT
      ',' + '"' + attribute + '":"' + STRING_ESCAPE(value,'json') + '"'
    FROM ProductAttributes a
        where a.product = p.product
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
  ) + '}' As attributes
from ProductAttributes p
group by product

开发人员将获得相同的结果

我已更新上面的SQL查询并在@Eilert的评论中使用了String_Escape()函数

- Eralper

---

好东西！在微软添加专用功能之前，这是一个不错的解决方法。谢谢。 - dNitro

我遇到了一个问题，即我试图将值转换为JSON时其中包含了特殊字符，从而弄乱了JSON。幸运的是，我偶然发现了STRING_ESCAPE函数，似乎正是为了解决这个特定问题而创建的：https://learn.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql - Eilert Hjelmeseth

非常感谢提供的信息，因此在代码块中用“STRING_ESCAPE（value，'json'）”替换“value”可能更安全。 - Eralper