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
Last activity: Jun 11, 2025, 09:45 AM