从Microsoft SQL Server中的加入表的列中删除正方形和卷曲括号

发布于 2025-01-30 04:20:56 字数 1682 浏览 4 评论 0原文

我正在使用Microsoft SQL Server v14.0.1000 我正在使用以下查询将查询结果转换为JSON：

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp AS EventTimeStamp,
       dn.ControllerId AS ControllerID,
       JSON_QUERY((SELECT ControllerName
                     FROM Controllers
                    WHERE dn.ControllerId = Controllers.ControllerID
                      FOR JSON PATH, without_array_wrapper)) AS ControllerName
  FROM DashboardNotifications dn
 ORDER BY dn.ID DESC
   FOR JSON PATH

并因此获取以下JSON：

[
  {
    "ID": 354,
    "EventTimeStamp": "2022-05-17T05:35:25",
    "ControllerId": 24,
    "ControllerName": {
      "ControllerName": "P25-SC-0233"
    }
  },
  {
    "ID": 353,
    "EventTimeStamp": "2022-05-17T05:34:20",
    "ControllerId": 17,
    "ControllerName": {
      "ControllerName": "P25-SC-0226"
    }
  },
  {
    "ID": 352,
    "EventTimeStamp": "2022-05-17T05:33:50",
    "ControllerId": 16,
    "ControllerName": {
      "ControllerName": "P25-SC-0225"
    }
  }
]

我能够从中删除

"ControllerName":[{"ControllerName":"P25-SC-0233"}],

方

[
  {
    "ID": 354,
    "EventTimeStamp": "2022-05-17T05:35:25",
    "ControllerId": 24,
    "ControllerName": "P25-SC-0233"
  },
  {
    "ID": 353,
    "EventTimeStamp": "2022-05-17T05:34:20",
    "ControllerId": 17,
    "ControllerName": "P25-SC-0226"
  },
  {
    "ID": 352,
    "EventTimeStamp": "2022-05-17T05:33:50",
    "ControllerId": 16,
    "ControllerName": "P25-SC-0225"
  }
]

括号嵌套控制器键？

原文

I'm using Microsoft SQL server v14.0.1000
I'm using the below query to convert my query result into JSON :

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp AS EventTimeStamp,
       dn.ControllerId AS ControllerID,
       JSON_QUERY((SELECT ControllerName
                     FROM Controllers
                    WHERE dn.ControllerId = Controllers.ControllerID
                      FOR JSON PATH, without_array_wrapper)) AS ControllerName
  FROM DashboardNotifications dn
 ORDER BY dn.ID DESC
   FOR JSON PATH

And getting the below JSON as a result :

[
  {
    "ID": 354,
    "EventTimeStamp": "2022-05-17T05:35:25",
    "ControllerId": 24,
    "ControllerName": {
      "ControllerName": "P25-SC-0233"
    }
  },
  {
    "ID": 353,
    "EventTimeStamp": "2022-05-17T05:34:20",
    "ControllerId": 17,
    "ControllerName": {
      "ControllerName": "P25-SC-0226"
    }
  },
  {
    "ID": 352,
    "EventTimeStamp": "2022-05-17T05:33:50",
    "ControllerId": 16,
    "ControllerName": {
      "ControllerName": "P25-SC-0225"
    }
  }
]

I've been able to remove square brackets from

"ControllerName":[{"ControllerName":"P25-SC-0233"}],

although my desired output is :

[
  {
    "ID": 354,
    "EventTimeStamp": "2022-05-17T05:35:25",
    "ControllerId": 24,
    "ControllerName": "P25-SC-0233"
  },
  {
    "ID": 353,
    "EventTimeStamp": "2022-05-17T05:34:20",
    "ControllerId": 17,
    "ControllerName": "P25-SC-0226"
  },
  {
    "ID": 352,
    "EventTimeStamp": "2022-05-17T05:33:50",
    "ControllerId": 16,
    "ControllerName": "P25-SC-0225"
  }
]

What changes do I need to make to remove the nested ControllerName key?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哑剧 2025-02-06 04:20:56

我认为您只需要json 的子查询（然后您不需要JSON_QUERY即可逃脱它）

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp AS EventTimeStamp,
       dn.ControllerId AS ControllerID,
       (
           SELECT c.ControllerName
             FROM Controllers c
            WHERE dn.ControllerId = c.ControllerID
       ) AS ControllerName
  FROM DashboardNotifications dn
 ORDER BY dn.ID DESC
   FOR JSON PATH;

确保您的子查询保证最多返回一排的

您也可以使用一个加入

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp,
       dn.ControllerId,
       c.ControllerName
  FROM DashboardNotifications dn
  LEFT JOIN
       Controllers c ON dn.ControllerId = c.ControllerID
 ORDER BY dn.ID DESC
   FOR JSON PATH;

I think you just want the subquery without FOR JSON (and then you don't need JSON_QUERY to escape it)

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp AS EventTimeStamp,
       dn.ControllerId AS ControllerID,
       (
           SELECT c.ControllerName
             FROM Controllers c
            WHERE dn.ControllerId = c.ControllerID
       ) AS ControllerName
  FROM DashboardNotifications dn
 ORDER BY dn.ID DESC
   FOR JSON PATH;

Ensure your subquery is guaranteed to return a maximum of one row

You could also just use a join

SELECT TOP(5) dn.id AS ID,
       dn.EventTimeStamp,
       dn.ControllerId,
       c.ControllerName
  FROM DashboardNotifications dn
  LEFT JOIN
       Controllers c ON dn.ControllerId = c.ControllerID
 ORDER BY dn.ID DESC
   FOR JSON PATH;

回复收藏 0 原文

九命猫 2025-02-06 04:20:56

绝对不需要JSON_QUERY。将查询更改为：

SELECT TOP(5) dn.id AS ID, dn.EventTimeStamp AS EventTimeStamp, dn.ControllerId AS ControllerID, (
    SELECT ControllerName FROM Controllers WHERE dn.ControllerId = Controllers.ControllerID
) AS ControllerName
FROM DashboardNotifications dn
ORDER BY dn.ID DESC
FOR JSON PATH

There is absolutely no need for json_query. Change the query to:

SELECT TOP(5) dn.id AS ID, dn.EventTimeStamp AS EventTimeStamp, dn.ControllerId AS ControllerID, (
    SELECT ControllerName FROM Controllers WHERE dn.ControllerId = Controllers.ControllerID
) AS ControllerName
FROM DashboardNotifications dn
ORDER BY dn.ID DESC
FOR JSON PATH

回复收藏 0 原文

~没有更多了~