SQL Server : build JSON dynamic key

user_10363660 0
Viewed 247

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.

sql-server
2 Answers

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
user_148689970

I would like to add another solution, perhaps for different cases.

In case you already have a generated JSON and you want to transform it, for example:

[
    {
        "key": "key1",
        "value": "value1"
    },
    {
        "key": "key2",
        "value": "value2"
    },
    {
        "key": "key3",
        "value": "value3"
    }
]

You can apply this sql script to transform it:

SET @json = REPLACE(@json, '","', '":"')
SET @json = REPLACE(@json, '"key":', '')
SET @json = REPLACE(@json, '"value":', '')
SET @json = REPLACE(@json, '{', '')
SET @json = REPLACE(@json, '}', '')
SET @json = REPLACE(@json, '[', '{')
SET @json = REPLACE(@json, ']', '}')

for the example, the result would transform into this:

{
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
}
user_123729920
Related