Sample Header Ad - 728x90

Merge two queries into one, involving aggregation

3 votes
1 answer
150 views
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
Asked by flourigh (145 rep)
Jun 8, 2025, 03:07 PM
Last activity: Jun 11, 2025, 09:45 AM