Convert Subquery Result to Array

I want my subquery results (multi rows) return as a singale row in my query. For example.


select u.url, (select c.categoryid from category c 
where c.categoryid = u.categoryid) from url u

If url contains multiple categories, subquery will return multiple rows as following
P.S Subquery return multiple rows is not supported in most of the database but PostgreSQL


url | categoryid
---------------------
1   |  1
1   |  2
2   |  1

Result i wanted is as following


url | categoryid
---------------------
1   |  1,2
2   |  1

I’m not sure how easy it can achieve in others database like oracle or mysql, however PostgreSQL provided Array data type, using array it’s very easy to get what i want. I have to changed my query to


select u.url, (select array(select c.categoryid from category c 
where c.categoryid = u.categoryid))as categoryid from url u

Result ~


url | categoryid
---------------------
1   |  {1,2}
2   |  1

Done ~ PostgreSQL Array Rock ~

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
7 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
bill42362PrabhatEzequielAri Bmkyong Recent comment authors
newest oldest most voted
bill42362
Guest
bill42362

SELECT u.url, array_agg(u.categoryid) AS categoryids
FROM url u
ORDER BY url

Ezequiel
Guest
Ezequiel

Hey there, does anyone know how can I read this output (an array column) using hibernate ? I’ve done this using array_to_string and then splitting the string column in Java, but I don’t like that. The any on SQLQuery was deprecated. custom and Hibernate.* types are deprecated also.

trackback
Bookmarks about Postgresql

[…] – bookmarked by 4 members originally found by lavishcouture on 2008-10-26 Convert Subquery Result to Array http://www.mkyong.com/database/convert-subquery-result-to-array/ – bookmarked by 4 members […]

Prabhat
Guest
Prabhat

thanks for the informative blog entries. I have the following problem and would appreciate your comments. I am trying to port the following Oracle sql to postgres: SELECT CAST (MULTISET (SELECT cd.a, cd.xml_key, cd.b, cd.c, cd.d, cd.e FROM table1 cd WHERE cd.a = t.a ) AS custom_typ )AS my_list FROM table2 t, table3 aclp WHERE t.foo = aclp.bar here the subquery has “multiple columns (not just one as in your example”. How could I convert the result set from the inner query into an array (of row set)?

Ezequiel
Guest
Ezequiel

Sorry, Forgot to check email notification

Ari B
Guest
Ari B

If you want the result 1,2 you can get it by using the array_to_string function

Like this :
SELECT u.url, (SELECT array_to_string(array(SELECT c.categoryid FROM category c
WHERE c.categoryid = u.categoryid)), ‘,’) AS categoryid FROM url u

Yura
Guest
Yura

Thanks a lot! I was wondering how to do this for one year!