将两列转换为键值json对象? [英] Convert two columns into key-value json object?

问题描述

在以下记录集(代表产品属性)上使用FOR JSON AUTO或FOR JSON PATH:

Using FOR JSON AUTO or FOR JSON PATH on the following record set (which representing a product's attributes):

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

将产生:

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

有什么方法可以产生以下内容?

Is there any way to produce the following Instead:

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

注意，因为每个产品属性都与其他属性不同；因此，每个产品的此记录集都是不同的. PIVOTing不是一个选项，因为它需要动态sql！似乎我们需要一个能够与产品表一起CROSS来生成例如产品目录的功能.

Note that as every product attribute is different than others; so this record set is different for every product. PIVOTing is not an option as it needs dynamic sql! Seems we need a function to be able to CROSS it with products table to produce for example a product catalog.

推荐答案

我使用了

Instead of JSON functions of SQL Server 2016, I used string concatenation function string_agg in SQL Server 2017 as seen in following script

/*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 {颜色":红色"，尺寸":小"} 2 {处理器":英特尔"，内存":"16"，重量":"2"}

Output is as follows for the two product entries product attributes 1 {"color":"red","size":"small"} 2 {"processor":"intel","ram":"16","weight":"2"}

如果使用的是SQL Server 2017之前的版本，则可以使用使用SQL XML路径进行字符串连接，如下所示

If you are using a previous version than SQL Server 2017, you can use string concatenation using SQL XML Path as follows

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

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

Developers will get the same result

我已经更新了上面的SQL查询，并使用了String_Escape()函数@Eilert的注释

I've updated above SQL queries and used String_Escape() function @Eilert's comment

这篇关于将两列转换为键值json对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！