I must provide query results in json format, one of the data strings is an array/list. I can provide the query and get a result but the system ingesting it requires it in a slightly different format, can anyone help with my query to achieve this.
We are querying a publications database, two tables, [publications] and a table linking them to user accounts [publications-users] a single result is shown below:
{
"_publication": {
"identifier": "a2v3h2t8e7w",
"authors": "Smith M; Jones G; Atkinson R",
"title": "A publication about something.",
"researchers": [
{
"uid": "user658"
},
{
"uid": "user663"
}
]
}
}
using code something like:
SELECT P.[id] as '_publication.identifier',
P.[authors] as '_publication.authors',
P.[title] as '_publication.title',
( SELECT PU.[userid] FROM [publications-users] PU WHERE P.[id]=PU.[pubid] FOR JSON PATH) as '_publication.researchers'
FROM [publications] P
FOR JSON PATH;
but the software receiving the json requires the researchers bit to be formatted as a CSV string:
{
"_publication": {
"identifier": "a2v3h2t8e7w",
"authors": "Smith M; Jones G; Atkinson R",
"title": "A publication about something.",
"researchers": [
{
"user658","user663"
}
]
}
}
I found STRING_AGG but this not available in Azure Data Studio
'string_agg' is not a recognized built-in function name.
Suggestions for best way to achieve this output please? It's probably really simple, I just can't get it!
SELECT @@VERSION;not About ADS)? How exactly are you referencingSTRING_AGG()?"researchers":[{850,853,856}]) is possible using nativeFOR JSONmethods. I come pretty close here by nestingFOR JSON PATHto get"researchers":[{"MeaninglessAlias":"850,853,856"}], but I don't know how to get rid of the meaningless alias or eliminate quotes around the string. You may need to explore more elaborate ways, e.g. this approach.