CODEWITHSUNDEEP
sqljsont-sqlssms
r2deeptube
r2deeptube

Reputation: 49

I want to combine json rows in t-sql into single json row

I have a table

id json
1 {"url":"url2"}
2 {"url":"url2"}

I want to combine these into a single statement where the output is :

{
    "graphs": [
        {
            "id": "1",
            "json": [
                {
                    "url": "url1"
                }
            ]
        },
        {
            "id": "2",
            "json": [
                {
                    "url": "url2" 
                }
            ]
        }
    ]
}

I am using T-SQL, I've notice there is some stuff in postgres but can't find much on tsql.

Any help would be greatly appreciated..

Upvotes: 0

Views: 355

Answers (1)

Charlieface
Charlieface

Reputation: 71364

You need to use JSON_QUERY on the json column to ensure it is not escaped.

SELECT
  id,
  JSON_QUERY('[' + json + ']')
FROM YourTable t
FOR JSON PATH, ROOT('graphs');

db<>fiddle

Upvotes: 2

Related Questions