How to split column headers & values in TSQL FOR JSON Option?

November 4, 2021

I have to talk to a webservice which accept JSON-messages, but is based on a dynamics princapel. so instead of using fixed defined fields, if wants a dynamics build, based on name\values attribute.

F.E.

{
    "Begin": [
        {
            "name": "sysrscols",
            "value": "sysrscols"
        },
        {
            "name": "id",
            "value": "3"
        },
        {
            "name": "crdate",
            "value": "2013-03-22T15:06:57.220"
        }
    ]
}

Using the FOR JSON option in SQL, I get a result with column names & their values.
F.e.
Query:

SELECT TOP 1 so.name,so.id,so.crdate  From sysobjects so FOR JSON PATH, Root('Begin')

Gives as result:

{
    "Begin": [
        {
            "name": "sysrscols",
            "id": 3,
            "crdate": "2013-03-22T15:06:57.220"
        }
    ]
}

How can I achieve the first result?

>Solution :

You’ll need to unpivot your data here, and then use FOR JSON. I use a dirty solution here and convert the values to a sql_variant; I suggest you do not do thus. Instead CONVERT the values to a (n)varchar and use the appropriate style codes where needed to get the "format" you want.

I also bring your code into the mid 2000’s, as the sys.sysobjects system view is a compatibility view for SQL Server 2000 (as noted in the documentation). As it’s 2021, I felt it long past time you started using the "new" system objects:

SELECT V.name,
       V.value
FROM sys.objects so
     CROSS APPLY (VALUES(CONVERT(sql_variant,so.name),N'name'),
                        (CONVERT(sql_variant,so.object_id),N'object_id'),
                        (CONVERT(sql_variant,so.create_date), N'createdate'))V([value],[name])
WHERE so.[name] = 'SomeView' --This would be the object you want
FOR JSON PATH, ROOT('Begin');