CODEWITHSUNDEEP
sqljsonsql-server
arresteddevelopment
arresteddevelopment

Reputation: 413

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

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.

Upvotes: 0

Views: 110

Answers (1)

Sergey Zakharov
Sergey Zakharov

Reputation: 316

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

Upvotes: 1

Related Questions