Create json key value from table column name and data

Question

Is it possible to create JSON key value from a table SELECT statement, where column name as key and the column value as value

declare @T table(Id int, ItemName varchar(10), CategoryId int, ItemDate date)
insert into @T
values(1,'ABC',100, '1/1/2020')

to return something as below

{
    "id": 1,
    "table": "tableName",
    "data": [{
            "key": "ItemName",
            "value": "ABC"
        },
        {
            "key": "CategoryId",
            "value": "100"
        },
        {
            "key": "ItemDate",
            "value": "1/1/2020"
        }
    ]
}

I have looked at selecting as JSON but stuck here

select *
from @T 
for json auto

The expected JSON output is not valid.

Zhorov
– Zhorov

2020-12-18 09:10:13 +00:00
Commented Dec 18, 2020 at 9:10 — Zhorov
– Zhorov, Commented Dec 18, 2020 at 9:10
@Zhorov: mistake, updated the question

user12073359
– user12073359

2020-12-18 09:16:51 +00:00
Commented Dec 18, 2020 at 9:16 — user12073359
– user12073359, Commented Dec 18, 2020 at 9:16

Zhorov · Accepted Answer · 2020-12-18 10:07:55Z

You may try to use VALUES table value constructor and FOR JSON AUTO. As is mentioned in the documentation, when ... you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables.

Table:

CREATE TABLE Tbl (
   Id int, 
   ItemName varchar(10), 
   CategoryId int, 
   ItemDate date
)
INSERT INTO Tbl
VALUES 
   (1, 'ABC', 100, '1/1/2020'),
   (2, 'DEF', 200, '2/2/2020')

Statement:

SELECT t.Id, data.[key], data.[value]
FROM Tbl t
CROSS APPLY (VALUES 
   ('ItemName', CONVERT(varchar(max), ItemName)),
   ('CategoryId', CONVERT(varchar(max), CategoryId)),
   ('ItemDate', CONVERT(varchar(max), ItemDate))
) Data ([key], [value])
FOR JSON AUTO

Result:

[
   {
      "Id":1,
      "Data":[
         {"key":"ItemName", "value":"ABC"},
         {"key":"CategoryId","value":"100"},
         {"key":"ItemDate","value":"2020-01-01"}
      ]
   },
   {
      "Id":2,
      "Data":[
         {"key":"ItemName", "value":"DEF"},
         {"key":"CategoryId", "value":"200"},
         {"key":"ItemDate", "value":"2020-02-02"}
      ]
   }
]

As an additional option you may try to build the inner JSON for each row:

SELECT 
   Id, 
   (
   SELECT [key], [value]
   FROM (VALUES
      ('ItemName', CONVERT(varchar(max), ItemName)),
      ('CategoryId', CONVERT(varchar(max), CategoryId)),
      ('ItemDate', CONVERT(varchar(max), ItemDate))
   ) v ([key], [value])
   FOR JSON PATH
   ) AS Data
FROM Tbl
FOR JSON AUTO

Collectives™ on Stack Overflow

Create json key value from table column name and data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related