How to create a single JSON array from two SELECT statements using SQL Server JSON functionality?

Question

I have the following SQL Statement:

SELECT
    (
        SELECT TOP 1
            Site.Reference,
            (
                SELECT TOP 1 ProjectId FROM Project WHERE Project.Siteid = Site.SiteId ORDER BY ProjectId ASC
            ) AS FirstId,
            (
                SELECT TOP 1 ProjectId FROM Project WHERE Project.Siteid = Site.SiteId ORDER BY ProjectId DESC
            ) AS LastId
        FROM 
            Site
        WHERE
            (Site.SiteId = 348)
        FOR JSON PATH, ROOT('seriesdata')
) AS JSONData

Site is the parent and Project is the child in a one-to-many relationship.

The output from this Query is:

JSONData
{"seriesdata":[{"Reference":"SIT0003237","FirstId":216,"LastId":1263}]}

The output I require is :

JSONData
{"seriesdata":[{"Reference":"SIT0003237","IdRange":[216,1263]}]}

I am failing to find how to implement this in SQL Server with the use of the JSON functionality. I would be grateful for any help to find a solution.

You can't create a simple array in SQL Server (missed opportunity). This would require a little string manipulation Take a peek at stackoverflow.com/questions/70511213/… — John Cappelletti
– John Cappelletti, Commented Feb 11, 2023 at 16:21
In SQL 2022 you can finally generate arrays! techcommunity.microsoft.com/t5/azure-sql-blog/… — siggemannen
– siggemannen, Commented Feb 11, 2023 at 16:30

Sergey Zakharov · Accepted Answer · 2023-02-11 18:17:54Z

Just join two tables and do the grouping by Reference, take the MIN and MAX from the Project Id, concatenate and wrap it in JSON_QUERY

SELECT (
  SELECT
    Site.Reference,
    JSON_QUERY('[' + CAST(MIN(ProjectId) AS VARCHAR) + ',' + CAST(MAX(ProjectId) AS VARCHAR) + ']') AS IdRange
    FROM Site
    JOIN Project ON Project.Siteid = Site.SiteId
    WHERE Site.SiteId = 348
    GROUP BY Site.Reference
    FOR JSON PATH, ROOT('seriesdata')
) AS JSONData

In the context of your question, the essence does not change - you need to concatenate two numbers into a json array as a string and wrap it in JSON_QUERY

SELECT (
  SELECT TOP 1
      Site.Reference,
      JSON_QUERY('[' + 
        (SELECT TOP 1 CAST(ProjectId AS VARCHAR) FROM Project WHERE Project.Siteid = Site.SiteId ORDER BY ProjectId ASC) +
        ','+
        (SELECT TOP 1 CAST(ProjectId AS VARCHAR) FROM Project WHERE Project.Siteid = Site.SiteId ORDER BY ProjectId DESC) +
      ']') AS IdRange
  FROM Site
  WHERE Site.SiteId = 348
  FOR JSON PATH, ROOT('seriesdata')
) AS JSONData

See example

Collectives™ on Stack Overflow

How to create a single JSON array from two SELECT statements using SQL Server JSON functionality?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related