如何使用SQL Server JSON功能从两个SELECT语句创建单个JSON数组？

zzlelutf 于 2023-02-14 发布在 SQL Server

关注(0)|答案(1)|浏览(164)

我有以下SQL语句：

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是父级，Project是子级。
此查询的输出为：

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

我需要的输出是：

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

我找不到如何在SQL Server中使用JSON功能实现这一点。如果能找到解决方案，我将非常感激。

JSON

来源：https://stackoverflow.com/questions/75421516/how-to-create-a-single-json-array-from-two-select-statements-using-sql-server-js

1条答案

按热度按时间

t2a7ltrp1#

只需连接两个表并通过引用进行分组，从ProjectId中获取MIN和MAX，将其连接并 Package 在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

在您的问题的上下文中，本质没有改变-您需要将两个数字作为字符串连接到json数组中，并将其 Package 在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

参见example

赞(0）回复(0）举报 2023-02-14

我来回答

如何使用SQL Server JSON功能从两个SELECT语句创建单个JSON数组？

1条答案

相关问题

热门标签

最新问答