Merge two queries into one, involving aggregation

Question

I have these two queries in Postgres.

One is:

SELECT _id, created_at
FROM pedidos
WHERE _id = '123abc;

Works fine, returns:

  {
    "_id": "123abc",
    "created_at": "Sun Jun 08 2025 10:28:23 GMT-0300 (Brasilia Standard Time)",
  }

The other:

SELECT i.id, i.objeto
FROM pedidos, jsonb_array_elements(objeto) WITH ORDINALITY i(objeto, id)
WHERE _id = '123abc';

Also works fine, returns:

  [
        {
            "id": "1",
            "objeto": {
                "peso": 76,
                "valor": 1290,
                "altura": 1001,
                "largura": 211,
                "comprimento": 298
            }
        },
        {
            "id": "2",
            "objeto": {
                "peso": 77,
                "valor": 1291,
                "altura": 1002,
                "largura": 212,
                "comprimento": 299
            }
        }
  ]

I can run both separately and merge to obtain:

  {
    "_id": "123abc",
    "created_at": "Sun Jun 08 2025 10:28:23 GMT-0300 (Brasilia Standard Time)",
    "objetos": [
        {
            "id": "1",
            "objeto": {
                "peso": 76,
                "valor": 1290,
                "altura": 1001,
                "largura": 211,
                "comprimento": 298
            }
        },
        {
            "id": "2",
            "objeto": {
                "peso": 77,
                "valor": 1291,
                "altura": 1002,
                "largura": 212,
                "comprimento": 299
            }
        }
    ]
  }

Is it possible to merge the two queries into one? I tried INNER JOIN but that returned:

i._id not found

Erwin Brandstetter · Accepted Answer · 2025-06-11T09:45:57.300

The basic issue is that a set-returning function like jsonb_array_elements() multiplies rows in the (implicitly LATERAL) join. Either avoid that by unnesting & aggregating in a subquery, or tell Postgres what to do with other columns when re-aggregating in the outer SELECT.

Aggregate in a subquery (without multiplying outer rows)

With a `LATERAL` subquery

Standard SQL.

SELECT p._id, p.created_at, sub.objetos
FROM   pedidos p
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(i.*) AS objetos
   FROM   jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)
   ) sub
WHERE  p._id = '123abc';

With a correlated subquery in the `SELECT` list

A bit snappier, but not strictly standard SQL.

SELECT _id, created_at
     , (SELECT jsonb_agg(i.*) FROM jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)) AS objetos
FROM   pedidos p
WHERE  _id = '123abc';

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Join first (multiplying rows), then aggregate

Unlike the top two variants, this eliminates rows where jsonb_array_elements() does not produce result rows. Work around with LEFT JOIN LATERAL ... ON true if required.

With `GROUP BY` for multiple result rows

SELECT p._id, p.created_at
     , jsonb_agg(i.*) AS objetos
FROM   pedidos p
     , jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)
WHERE  p._id = '123abc'
GROUP  BY 1, 2;

GROUP BY 1, 2 uses ordinal references, short for GROUP BY _id, created_at in this case.

Without `GROUP BY` for single result row

Probably applicable in this case if _id is the PK (or otherwise guaranteed to be unique), then WHERE p._id = '123abc' always filters a single row.

Using any_value() (Postgres 16+):

SELECT any_value(p._id) AS _id
     , any_value(p.created_at) AS created_at
     , jsonb_agg(i.*) AS objetos
FROM   pedidos p
     , jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)
WHERE  p._id = '123abc';

In older versions you had to work around with an arbitrary aggregate function like min():

SELECT min(p._id) AS _id
     , min(p.created_at) AS created_at
     , jsonb_agg(i.*) AS objetos
FROM   pedidos p
     , jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)
WHERE  p._id = '123abc';

All JSON output?

It's unclear how your results are magically converted to JSON documents. I'll assume that's what your client does for you. If you need to do it yourself (optionally prettified):

SELECT jsonb_pretty(to_jsonb(sub.*)) AS pretty_result  -- jsonb orders keys
     , to_json(sub.*) AS json_result                   -- json keeps org. order of keys
FROM  (
   -- any of the above queries here
   SELECT _id, created_at
        , (SELECT jsonb_agg(i.*) FROM jsonb_array_elements(p.objeto) WITH ORDINALITY i(objeto, id)) AS objetos
   FROM   pedidos p
   WHERE  _id = '123abc'
   ) sub;

fiddle

Merge two queries into one, involving aggregation

1 Answers1

Aggregate in a subquery (without multiplying outer rows)

With a LATERAL subquery

With a correlated subquery in the SELECT list