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

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..

CodePudding user response：

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