Search code examples
sqlpostgresqlhaving

Postgresql, jsonb_build_object and jsonb_agg

Recently I have come across two queries as:

select jsonb_build_object('a', jsonb_agg(1))
 where false;

select jsonb_build_object('a', jsonb_agg(1))
 where false
having count(*) > 0;

First retuns {"a": null}, second - nothing, could you please explain me what happens in these 2 queries? I would expect nothing in result for BOTH.

Solution

  • It is not related to JSON functions.

    select count(1) where false;

    and

    select count(1) where false having count(1) > 0;

    providing same effect.

    First query reporting that there is 0 records satisfied given condition in the where clause and in the second query having clause filtering out rows from the first query where count(1) <= 0