SQL how to create a JSON array from all the values in a single column

Question

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!

Open stackoverflow.com/questions/41758870/… . I think it will help you. — Rajender Kumar
– Rajender Kumar, Commented Oct 15, 2021 at 6:40

Jawwad Ahmed · Accepted Answer · 2021-10-15 06:39:03Z

0

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

answered Oct 15, 2021 at 6:39

Jawwad Ahmed

3172 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefanov.sm · Accepted Answer · 2021-10-15 07:33:43Z

0

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

edited Oct 15, 2021 at 7:33

answered Oct 15, 2021 at 7:14

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL how to create a JSON array from all the values in a single column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related