CODEWITHSUNDEEP
postgresql
Jerry Jones
Jerry Jones

Reputation: 796

SELECT statement using JOIN table with array data type in PostgreSQL

I am having two tables named 'tbl_users' and 'tbl_order' where tbl_users having three columns 'id', 'name', 'items' where column 'items' data type is array and 'tbl_orders' having two columns 'id', 'item'. I want to select all the data and their orders using SELECT and JOIN. The end result should contain 'id', 'name', 'item' where column 'item' should contain all the items a user has ordered. Am using postgreSQL as database. Issue is I can join the tables but since the data type is array it ives error while performing JOIN. Anybody suggest a proper syntax for performing JOIN with array data type.

Upvotes: 0

Views: 462

Answers (1)

Ancoron
Ancoron

Reputation: 2743

PostgreSQL supports array operators you can use to resolve the identifiers to their respective order item:

SELECT
    u.id,
    u.name,
    CASE WHEN max(o.item) IS NULL THEN NULL ELSE array_agg(o.item) END AS items
FROM tbl_users AS u
LEFT JOIN tbl_orders AS o ON (u.items @> array[o.id])
GROUP BY 1, 2;

Online demo: https://rextester.com/CZDC2260

...would return:

 id | name | items 
----+------+-------
  1 | john | {A,B}
  2 | kate | {A}
  3 | sue  | 
  4 | ted  | 
(4 rows)

Upvotes: 2

Related Questions