How to get an empty array in array_agg if condition is not met?

Question

I have 2 tables:

collections_books (collection_id, book_id)
users_collections (user_id, collection_id, access)

I'm using PostgreSQL.

The following query gives me a list of book_ids grouped by collection_ids. The problem is, since I'm using where condition, the results are limited to only the collections allowed for user_id = 3.

But, I want all the collection_ids and the corresponding book_ids

As an array, if the user_id has access = allow
As an empty array, if the user_id is not present in the users_collections or user_id != allow

SELECT c.collection_id, ARRAY_AGG(c.book_id)
FROM collections_books AS c
LEFT JOIN users_collections AS u
ON c.collection_id = u.collection_id
WHERE 
  u.access = 'allow' AND
  u.user_id = 3
GROUP BY c.collection_id;

Kazi Mohammad Ali Nur Romel · Answer 1 · 2021-03-25T06:42:13.953

2

You check array_length(). If it's less then 1 then return a array with of value {null}

If you want to get all the collection_id from collections_books but array of book_id only if u.access = 'allow' AND u.user_id = 4 other wise null then use below query:

SELECT c.collection_id, 
    (
        CASE 
             WHEN max(u.access) = 'allow' AND max(u.user_id) = 4
             THEN ARRAY_AGG(c.book_id)
             ELSE '{null}'::int[] 
        END
    )
    FROM collections_books AS c
    LEFT JOIN users_collections AS u
    ON c.collection_id = u.collection_id
    GROUP BY c.collection_id;

edited Mar 25 '21 at 06:42

answered Mar 25 '21 at 05:35

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

It doesn't return all the `collection_id`s though. It only returns the `collection_id` where `user_id=3` and `access='allow'`. I want the collections that doen't satisfy the condition too, but with the `array_agg(c.book_id)` as an empty array. – Siddiq Nx Mar 25 '21 at 05:48
Because those are condition of where clause. Other rows will be ignored – Kazi Mohammad Ali Nur Romel Mar 25 '21 at 05:49
Yeah, I don't want that to happen. I don't want to ignore other rows. What should iI do? – Siddiq Nx Mar 25 '21 at 05:51
I have modified my answer. Please check. – Kazi Mohammad Ali Nur Romel Mar 25 '21 at 05:52
Now it shows the book_ids of all the collections. Iit even shows `book_id`s where the `user_id=3` has `access != 'allow'`. Meaning it shows all the `book_id`s for all the collections. I want empty array for the `collections` where `access != 'allow'` – Siddiq Nx Mar 25 '21 at 05:57
but you want to have all the collection_id. Right? – Kazi Mohammad Ali Nur Romel Mar 25 '21 at 06:00
Yeah. But the `collection_id`s for which the user has no access (`access != 'allow'`) should have the `array_agg(book_id)` as empty array. Only the collection that the given user has _access_ to, will have a non null `array_agg(book_id)`. – Siddiq Nx Mar 25 '21 at 06:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230350/discussion-between-kazi-mohammad-ali-nur-and-siddiq-nx). – Kazi Mohammad Ali Nur Romel Mar 25 '21 at 06:03

score 1 · Accepted Answer · edited Mar 25 '21 at 06:51

Please check out below answer and let me know whether it returns your desired output or not:

Schema and insert statements:

     create table users_collections (user_id int, collection_id int, access varchar(20));
     insert into users_collections values(3, 1, 'allow');
     insert into users_collections values(3, 2, 'allow');
     insert into users_collections values(4, 3, 'allow');
     insert into users_collections values(3, 5, 'not allow');

 
     create table collections_books (collection_id int, book_id int);
     insert into collections_books values(2,24);
     insert into collections_books values(3,35);
     insert into collections_books values(3,25);
     insert into collections_books values(1,36);
     insert into collections_books values(1,22);
     insert into collections_books values(1,24);
     insert into collections_books values(2,34);
     insert into collections_books values(5,344);
     insert into collections_books values(6,474);

Query:

     SELECT c.collection_id, (CASE WHEN max(u.access) = 'allow' AND max(u.user_id) = 3
     THEN ARRAY_AGG(c.book_id)
     ELSE '{null}'::int[] END)
     FROM collections_books AS c
     LEFT JOIN users_collections AS u
     ON c.collection_id = u.collection_id
     GROUP BY c.collection_id;

Output:

 |collection_id | case      |
 |------------: | :---------|
 |            3 | {35,25}   |
 |            5 | {NULL}    |
 |            6 | {NULL}    |
 |            2 | {24,34}   |
 |            1 | {36,24,22}|

db<fiddle here

I've edited the when clause (added `max(u.user_id) = 3`) and it works exactly how I wanted! — Siddiq Nx, Mar 25 '21 at 06:47

How to get an empty array in array_agg if condition is not met?

2 Answers2