1

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?

2

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.

0

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

4
  • I could certainly do that, but I'd rather avoid the subquery (it causes some other difficulties in my context). This doesn't explain why postgres lets you group by the alias but not by derivative expressions based on the alias.
    – jstaab
    Jul 22 at 23:54
  • yes it explains it very clearly, a cplumn for a group by can be taken from the select clause,that is why i placed it in a subquery so that it doesn't
    – nbk
    Jul 22 at 23:58
  • 1
    "a has to exist prior to the group by" is incorrect: aliases can be used in group by -- but only by themselves and not as part of an expression. Jul 23 at 9:18
  • so my is correct, you can't use a as json as he want a value of that json as group by amnd not a column values
    – nbk
    Jul 23 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.