SQL Server table data to JSON Path result

Question

I am looking for a solution to convert the table results to a JSON path.

I have a table with two columns as below. Column 1 Will always have normal values, but column 2 will have values up to 15 separated by ';' (semicolon).

ID   Column1               Column2
--------------------------------------
 1      T1               Re;BoRe;Va

I want to convert the above column data in to below JSON Format

{
    "services": 
    [
        { "service": "T1"}
    ], 
    "additional_services": 
    [
        { "service": "Re" }, 
        { "service": "BoRe" }, 
        { "service": "Va" }
    ]
}

I have tried creating something like the below, but cannot get to the exact format that I am looking for

SELECT 
    REPLACE((SELECT d.Column1 AS services, d.column2 AS additional_services
             FROM Table1 w (nolock)
             INNER JOIN Table2 d (nolock) ON w.Id = d.Id
             WHERE ID = 1
             FOR JSON PATH), '\/', '/')

Please let me know if this is something we can achieve using T-SQL

You really need to fix your design; if Re;BoRe;Va represent 3 different items, it should be 3 rows. Don't store delimited data in your database. — Thom A
– Thom A ♦, Commented Mar 31, 2022 at 16:14

Thom A · Accepted Answer · 2022-03-31 16:21:15Z

1

As I mention in the comments, I strongly recommend you fix your design and normalise your design. Don't store delimited data in your database; Re;BoRe;Va should be 3 rows, not 1 delimited one. That doesn't mean you can't achieve what you want with your denormalised data, just that your design is flawed, and thus it needs being brought up.

One way to achieve what you're after is with some nested FOR JSON calls:

SELECT (SELECT V.Column1 AS service
        FOR JSON PATH) AS services,
        (SELECT SS.[value] AS service 
         FROM STRING_SPLIT(V.Column2,';') SS
         FOR JSON PATH) AS additional_services
FROM (VALUES(1,'T1','Re;BoRe;Va'))V(ID,Column1,Column2)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

This results in the following JSON:

{
    "services": [
        {
            "service": "T1"
        }
    ],
    "additional_services": [
        {
            "service": "Re"
        },
        {
            "service": "BoRe"
        },
        {
            "service": "Va"
        }
    ]
}

answered Mar 31, 2022 at 16:21

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQL Starter Over a year ago

Thank you for suggesting the change, will definitely keep it in mind. The code worked perfectly!

Collectives™ on Stack Overflow

SQL Server table data to JSON Path result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related