SQL Server : build JSON dynamic key

Question

I'm trying to create object with dynamic key and value with SQL Server. Currently I have the following query result:

[ 
    { "Name": "Ashley", "type": "human" },
    { "Name": "Dori", "type": "cat" }
]

and I am looking to build this:

[ { "Human": "Ashley" }, { "Cat": "Dori" } ]

PS: I'm not sure if it's even possible, but I have no ideas left.

Answer 1

If you know every possible combination up front, you can specify them in CASE expressions like this

SELECT
    Human = CASE WHEN t.[type] = 'human' THEN t.Name END,
    Cat =   CASE WHEN t.[type] = 'cat'   THEN t.Name END
-- more options
FROM YourTable t
FOR JSON PATH;

---

If you don't know what the possible options are, you will need dynamic SQL.

Try to keep a clear head about which part is static and which is dynamic. Use PRINT @sql; to test.

DECLARE @sql nvarchar(max) = '
SELECT
' + (
    SELECT STRING_AGG(CAST(
'    ' + typeCol + ' = CASE WHEN t.[Type] = ' + typeStr + ' THEN t.Name END'
        AS nvarchar(max)), ',
')
    FROM (
        SELECT DISTINCT
           typeCol = QUOTENAME(UPPER(LEFT(t.[type], 1)) + SUBSTRING(t.[type], 2, LEN(t.[type]))),
           typeStr = QUOTENAME(t.[type], '''')
        FROM YourTable t
    )
) + '
FROM YourTable t
FOR JSON PATH;
';

PRINT @sql; --for testing

EXEC sp_executesql @sql;  -- add parameters if necessary