SELECT statement using JOIN table with array data type in PostgreSQL

Question

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.

https://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement — Frankich, Mar 08 '19 at 11:37
Possible duplicate of [PostgreSQL JOIN with array type with array elements order, how to implement?](https://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement) — Dave, Mar 08 '19 at 11:42
Please **[edit]** your question (by clicking on the [edit] link below it) and add some [sample data](https://meta.stackexchange.com/questions/81852) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Mar 08 '19 at 11:55
You _can_ join using array columns, you just have to use the correc [array operator](https://www.postgresql.org/docs/current/functions-array.html) — , Mar 08 '19 at 11:55

score 2 · Accepted Answer · answered Mar 08 '19 at 11:56

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)

SELECT statement using JOIN table with array data type in PostgreSQL

1 Answers1