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.
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
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"
}
"\"{[\"key\",":"][}\":" now what's going to happen?or