0

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!

5
  • Function has nothing to do with Azure Data Studio, what version of SQL Server are you running (SELECT @@VERSION; not About ADS)? How exactly are you referencing STRING_AGG()? Commented May 16, 2022 at 16:11
  • Good point ... it's Microsoft SQL Server 2016 (SP3) so STRING_AGG not available. I can't upgrade this, not my server, is there another way? Commented May 16, 2022 at 16:56
  • Can you show the source tables and a few rows of sample data? Maybe a fiddle? Commented May 16, 2022 at 20:15
  • have done this as a fiddle dbfiddle.uk/… Commented May 17, 2022 at 9:36
  • I'm not sure if the exact output you want ("researchers":[{850,853,856}]) is possible using native FOR JSON methods. I come pretty close here by nesting FOR JSON PATH to 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. Commented May 17, 2022 at 17:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.