sqljsonsql-serversql-server-2016sql-server-json

Create json key value from table column name and data


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

Solution

  • 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