Remove the square and curly brackets from the column of joined table in Microsoft SQL server-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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