Postgresql group by aliased jsonb field

All we need is an easy explanation of the problem, so here it is.

I’m trying to group by the result of a jsonb operation on an aliased field, but getting an error I would not expect.

The following work as expected:

select jsonb_build_object('x', 1) as a group by a;
select jsonb_build_object('x', 1) as a group by jsonb_build_object('x', 1)#>>'{x}';

    a     
----------
 {"x": 1}

But this gives me an error:

select jsonb_build_object('x', 1) as a group by a#>>'{x}';
ERROR:  column "a" does not exist

Is this a bug in postgresql (13.3)? Is there any way around it other than repeating the entire select expression in the group by?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use an alias (or a number referencing the position in the SELECT list) only when it stands alone and is not used inside an expression.

The documentation is not quite clear about that; all it says is

In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

Method 2

The problem is thats you want to use a values of the json, but you can access his value at that position, what causes the error message

If you need need this, you need top

SELECT a FROM (select jsonb_build_object('x', 1) as a) t1  group by a#>>'{x}';

As the other option for using select columns in a Group by, doesn’t allow you to select a value of the json, only the complete content of column

see also the manual

example

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply