CODEWITHSUNDEEP
sqljsonsql-servert-sql
Reto E.
Reto E.

Reputation: 119

Create JSON with column name and value as key-value pairs

We have to format a data export as JSON with a certain format. Here a test case:

DECLARE @tmp TABLE (ID INT, value1 VARCHAR(30), value2 VARCHAR(30))
INSERT @tmp VALUES (1,'test1','test2')

Output should be:

{
    "ID": 1,
    "custom_attributes": [
        {
            "attribute_code": "value1",
            "value": "test1"
        },
        {
            "attribute_code": "value2",
            "value": "test2"
        }
    ]
}

We have to make two key-value pairs for some columns. The first describes the column name and the second the value. And on top pack it into an array.

I wonder if this is possible with native t-sql JSON functions, because I need a proper JSON as output (and not a string).

Upvotes: 0

Views: 3180

Answers (2)

Reto E.
Reto E.

Reputation: 119

I found a possible answer, which works, but seems to be a bit complicated ...

SELECT tmp.ID
,      (
        SELECT ca.attribute_code
        ,      ca.[value]
        FROM @tmp tmp2
        CROSS APPLY (
            VALUES 
                ('value1',value1),
                ('value2',value2)
        ) ca (attribute_code, [value])
        WHERE tmp2.ID = tmp.ID
        FOR JSON PATH
        ) [custom_attributes]
FROM @tmp tmp
FOR JSON PATH

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272406

You need to use nested FOR JSON:

DECLARE @tmp TABLE(ID INT, value1 VARCHAR(30), value2 VARCHAR(30));
INSERT @tmp VALUES (1, 'test1', 'test2');

SELECT ID, custom_attributes = (
    SELECT attribute_code, value
    FROM @tmp AS x
    -- column to rows
    CROSS APPLY (VALUES
        ('value1', value1),
        ('value2', value2)
    ) AS a(attribute_code, value)
    WHERE x.ID = t.ID
    FOR JSON PATH
)
FROM @tmp AS t
FOR JSON AUTO

Upvotes: 2

Related Questions