Postgresql group by aliased jsonb field

group bypostgresql

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?

Best Answer

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.

FIRST PHASE OF QUERY

SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS kumar_concat;
Query OK, 3 rows affected (0.03 sec)

mysql> CREATE DATABASE kumar_concat;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar_concat
Database changed
mysql> CREATE TABLE table1
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table1 VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table2
    -> (
    ->     id INT NOT NULL,
    ->     env VARCHAR(10) NOT NULL,
    ->     infid INT NOT NULL,
    ->     PRIMARY KEY (id,infid)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO table2 (id,env,infid) VALUES
    -> (1,'P',10), (1,'P',11), (1,'P',20),
    -> (1,'P',12), (1,'D',21), (1,'D',22);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table3
    -> (
    ->     infid INT NOT NULL,
    ->     inftype VARCHAR(10) NOT NULL,
    ->     infname VARCHAR(10) NOT NULL,
    ->     PRIMARY KEY (infid)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO table3 (infid,inftype,infname) VALUES
    -> (10,'Srv','abc'), (20,'Srv','xyz'), (11,'Db','hgj'),
    -> (12,'Db','kjk'), (21,'Srv','pop'), (22,'Db','kli');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

FIRST PHASE OF QUERY EXECUTED

mysql> SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env;
+-----+------------------------------+
| env | tags                         |
+-----+------------------------------+
| D   | Srv [pop] Db [kli]           |
| P   | Srv [abc, xyz] Db [hgj, kjk] |
+-----+------------------------------+
2 rows in set (0.02 sec)

mysql>

Let's create each for each env value

QUERY

SELECT
    T1.tags P,T2.tags D
FROM
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T1,
(SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY env,inftype) A GROUP BY env) T2
WHERE T1.env='P'
AND T2.env='D';

QUERY EXECUTED

mysql> SELECT
    ->     T1.tags P,T2.tags D
    -> FROM
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T1,
    -> (SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY env,inftype) A GROUP BY env) T2
    -> WHERE T1.env='P'
    -> AND T2.env='D';
+------------------------------+--------------------+
| P                            | D                  |
+------------------------------+--------------------+
| Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+------------------------------+--------------------+
1 row in set (0.05 sec)

mysql>

FINAL QUERY (with id wedged in)

SELECT
    T1.id,T1.tags P,T2.tags D
FROM
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T1
INNER JOIN
(SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN table2 BBB ON AAA.id = BBB.id
INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
GROUP BY id,env,inftype) A GROUP BY id,env) T2 
USING (id) WHERE T1.env='P' AND T2.env='D';

FINAL QUERY (with id wedged in) EXECUTED

mysql> SELECT
    ->     T1.id,T1.tags P,T2.tags D
    -> FROM
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T1
    -> INNER JOIN
    -> (SELECT id,env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
    -> ORDER BY inftype DESC SEPARATOR ' ') tags
    -> FROM (SELECT id,env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
    -> FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
    -> FROM table1 AAA
    -> INNER JOIN table2 BBB ON AAA.id = BBB.id
    -> INNER JOIN table3 CCC ON BBB.infid = CCC.infid) AA
    -> GROUP BY id,env,inftype) A GROUP BY id,env) T2
    -> USING (id) WHERE T1.env='P' AND T2.env='D';
+----+------------------------------+--------------------+
| id | P                            | D                  |
+----+------------------------------+--------------------+
|  1 | Srv [abc, xyz] Db [hgj, kjk] | Srv [pop] Db [kli] |
+----+------------------------------+--------------------+
1 row in set (0.08 sec)

mysql>

GIVE IT A TRY !!!

Postgresql – Querying JSONB in PostgreSQL

This does not work because it's trying to cast a jsonb value to integer.

select data->'name' as name from persons where cast(data->'age' as int) > 25

This would actually work:

SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;

Or shorter:

SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;

And this:

SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';

Seems like confusion with the two operators -> and ->> and operator precedence. The cast :: binds stronger than the json(b) operators.

Figure out type dynamically

This is the more interesting part of your question:

the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?

SQL is a strictly typed language, it does not allow the same expression to evaluate to integer in one row and to text in the next. But since you are only interested in the boolean result of the test, you can get around this restriction with a CASE expression that forks depending on the result of jsonb_typeof():

SELECT data->'name'
FROM   persons
WHERE  CASE jsonb_typeof(data->'age')
        WHEN 'number'  THEN (data->>'age')::numeric > '25' -- treated as numeric
        WHEN 'string'  THEN data->>'age' > 'age_level_3'   -- treated as text
        WHEN 'boolean' THEN (data->>'age')::bool           -- use boolean directly (example)
        ELSE FALSE                                         -- remaining: array, object, null
       END;

An untyped string literal to the right of the > operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.

If you know that all numeric values are actually integer, you can also:

... (data->>'age')::int > 25 ...