this query below returns a single column from which I want to create a JSON array from:
SELECT CP.PageID
FROM CommClient.dbo.ClientPage AS CP
INNER JOIN CommApp.dbo.Page as P ON CP.PageID = P.PageID
INNER JOIN CommApp.dbo.PageGroup as PG ON P.PageGroupID = PG.PageGroupID
LEFT JOIN (
SELECT DISTINCT ParentPageID FROM [CommApp].[dbo].[Page]
) AS PP
ON P.PageID = PP.ParentPageID
WHERE ClientID = 102 AND P.ApplicationID = 4
ORDER BY P.pageID
The result I need is and array of values like this: [4000, 4010, 4110 ...]
I've tried to add FOR JSON PATH at the end of that query but I'm getting an array of object ([{"pageID":4000},{"pageID":4010},{"pageID":4110} ....) instead of an array of values and I'm at a loss, I'm a newb in SQL... Thanks!
CodePudding user response:
There is a function named GROUP_CONCAT in mySQL you can write as follows. Note the group by clause in the end and group_concat in select
SELECT GROUP_CONCAT(CP.PageID) as PageID
FROM CommClient.dbo.ClientPage AS CP
INNER JOIN CommApp.dbo.Page as P ON CP.PageID = P.PageID
INNER JOIN CommApp.dbo.PageGroup as PG ON P.PageGroupID = PG.PageGroupID
LEFT JOIN (
SELECT DISTINCT ParentPageID FROM [CommApp].[dbo].[Page]
) AS PP
ON P.PageID = PP.ParentPageID
WHERE ClientID = 102 AND P.ApplicationID = 4
GROUP BY CP.PageID
ORDER BY P.pageID
CodePudding user response:
It varies in different DBMS.
SQL Server:
select '[' string_agg(<expression>, ',') ']'
-- the rest of your query
Postgres:
select json_agg(<expression>)
-- the rest of your query
Oracle:
select json_arrayagg(<expression>)
-- the rest of your query