SELECT with array values on WHERE using postgres
1
vote
1
answer
61
views
I'm using an query to update a object array inside a **jsonb** column.
Example data:
[
{
"_id": "68696e0a3aab2f9ff9c40679",
"altura": 1,
"comprimento": 1,
"largura": 1,
"peso": 1,
"valor": 1
},
{
"_id": "6869744b44829f42ccdbb32c",
"altura": 2,
"comprimento": 2,
"largura": 2,
"peso": 2,
"valor": 2
}
]
Using one ID, this works perfectly:
UPDATE
objetos o
SET
itens = o.itens - (
SELECT
i.id::int - 1
FROM
jsonb_array_elements(o.itens) WITH ORDINALITY i(v, id)
WHERE
i.v->'_id' = '6869744b44829f42ccdbb32c'
LIMIT 1
)
WHERE
_id = ${_id}
RETURNING
_id,
updated_at;
It deletes a entry containing _id = 6869744b44829f42ccdbb32c
---
I have tried to delete entries using ARRAY ids, example ['68696e0a3aab2f9ff9c40679', '6869744b44829f42ccdbb32c']
, but I get:
~~~none
operator does not exist: jsonb = text
~~~
I'm trying add this in WHERE
:
i.v->'_id' = ANY(ARRAY['68696e0a3aab2f9ff9c40679', '6869744b44829f42ccdbb32c'])
and IN
, but IN
does not return any information.
How to compare i.v->'_id'
to elements of an array? Like:
['68696e0a3aab2f9ff9c40679', '6869744b44829f42ccdbb32c'].includes(i.v->'_id')
References:
1. https://stackoverflow.com/a/10738459/2741415
2. https://dba.stackexchange.com/a/315124/321838
3. https://stackoverflow.com/a/75053441/2741415
Asked by flourigh
(145 rep)
Jul 5, 2025, 07:34 PM
Last activity: Jul 10, 2025, 12:46 AM
Last activity: Jul 10, 2025, 12:46 AM