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
will produce:
[{"attribute":"color","value":"red"},{"attribute":"size","value":"small"}]
Is there any way to produce the following Instead:
{"color":"red","size":"small"}
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
Output is as follows for the two product entries
product attributes
1 {"color":"red","size":"small"}
2 {"processor":"intel","ram":"16","weight":"2"}
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
I've updated above SQL queries and used String_Escape() function @Eilert's comment