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 · Accepted Answer · 2021-03-25 06:42:13Z

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, 2021 at 6:42

answered Mar 25, 2021 at 5:35

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

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

8 Comments

Siddiq Nx Over a year ago

It doesn't return all the collection_ids 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.

Kazi Mohammad Ali Nur Romel Over a year ago

Because those are condition of where clause. Other rows will be ignored

Siddiq Nx Over a year ago

Yeah, I don't want that to happen. I don't want to ignore other rows. What should iI do?

Kazi Mohammad Ali Nur Romel Over a year ago

I have modified my answer. Please check.

Siddiq Nx Over a year ago

Now it shows the book_ids of all the collections. Iit even shows book_ids where the user_id=3 has access != 'allow'. Meaning it shows all the book_ids for all the collections. I want empty array for the collections where access != 'allow'

|

Siddiq Nx · Accepted Answer · 2021-03-25 06:51:20Z

1

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

edited Mar 25, 2021 at 6:51

Siddiq Nx

1221 silver badge7 bronze badges

answered Mar 25, 2021 at 6:40

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

1 Comment

Siddiq Nx Over a year ago

I've edited the when clause (added max(u.user_id) = 3) and it works exactly how I wanted!

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related