I need to generate a JSON array containing two dissimilar objects:
[
{
"command": "setcontext",
"recordtype": "client",
"recordid": 1030
},
{
"command": "gotodq",
"usercode": "react_webuser_debtor_details"
}
]
I'm able to generate the two objects separate by using:
SELECT
'setcontext' AS command, 'client' AS recordtype, 1030 AS recordid
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
SELECT
'gotodq' AS command, 'react_webuser_debtor_details' AS usercode
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
but I can't find out how to join those two statements into one to get the array.
Obviously I can concatenate both as strings appending the array markers but I'm curious on how a "pure" SQL to JSON solution would look...
1条答案
按热度按时间gab6jxml1#
In SQL Server 2022 and Azure SQL, you can use the
JSON_ARRAY
andJSON_OBJECT
functionsIn older versions, you can't use those. Also
JSON_ARRAY_AGG
also isn't available.You could just concatenate them. (Use
STRING_AGG
if you have an undefined number of items.)Or you could do it as a
UNION ALL
, but then columns with the same name would need to be the same data type.db<>fiddle