Output key value pairs as JSON from T-SQL query

user_6447256 0
Viewed 574

I have an app that needs to be supplied with configuration data as key value pairs in JSON.

I want to output JSON of key value pairs from a table query.

The table can have different named items and varying quanities of items.

TABLE

ITEMID REPORTID NAME VALUE
1 29 caption Profit by Product
2 29 bgColor #FFFFFF
3 29 showBorder 0
4 29 showsum 0

SQL

select .................   FOR JSON PATH

I'm guessing this needs to use a PIVOT?

Expected JSON

{"caption":"Profit by Product","bgColor":"#FFFFFF","showBorder":"0","showsum":"0"}

Appreciate help anyone can provide.

tsql json
2 Answers

Your can do a little String Aggregation (assuming not 2017)

Example

Declare @YourTable Table ([ITEMID] varchar(50),[REPORTID] varchar(50),[NAME] varchar(50),[VALUE] varchar(50))  Insert Into @YourTable Values 
 (1,29,'caption','Profit by Product')
,(2,29,'bgColor','#FFFFFF')
,(3,29,'showBorder','0')
,(4,29,'showsum','0')


Select REPORTID
      ,JSONString =  '{' + Stuff((Select concat(',','"',Name,'":"',value,'"') From @YourTable Where ReportID=A.ReportID For XML Path ('')),1,1,'')  + '}'
 From @YourTable A
 Group by REPORTID

Returns

REPORTID    JSONString
29          {"caption":"Profit by Product","bgColor":"#FFFFFF","showBorder":"0","showsum":"0"}
user_15700000

A pivot query along with FOR JSON PATH should generate the JSON output you expect:

SELECT
    MAX(CASE WHEN NAME = 'caption' THEN VALUE END) AS caption,
    MAX(CASE WHEN NAME = 'bgColor' THEN VALUE END) AS bgColor,
    MAX(CASE WHEN NAME = 'showBorder' THEN VALUE END) AS showBorder,
    MAX(CASE WHEN NAME = 'showsum' THEN VALUE END) AS showsum
FROM yourTable
GROUP BY
    ITEMID
FOR JSON PATH;

enter image description here

Demo

Note that if your table contains more than one ITEMID group, then there would be one JSON record in the output per item.

user_18632290
Related