1

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:

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
Improve this question
2
  • That sounds like all you have to do is to use i.v->>'_id' so that you get the result as a text rather than as a jsonb. Commented Jul 7 at 7:25
  • The essential table definition is missing. Commented Jul 10 at 0:42

1 Answer 1

Reset to default
1

For this particular case, where all you want to do is delete an item out of an array, the easiest option is to use JsonPath to query all the array values except the one you want to delete, then just set the column to that array.

You can pass the value in using the variables parameter of jsonb_path_query_array

UPDATE objectos o
SET itens = jsonb_path_query_array(
  o.itens,
  '$[*] ? (@._id != $idToSearch)',
  '{"idToSearch":"6869744b44829f42ccdbb32c"}')
RETURNING *;

So with an array of values to search for, you can do similar:

UPDATE objectos o
SET itens = jsonb_path_query_array(
  o.itens,
  'strict $[*] ? (! (@._id == $idToSearch[*]))',
  '{"idToSearch":["6869744b44829f42ccdbb32c","68696e0a3aab2f9ff9c40679"]}')
RETURNING *;

The weird !(x == y[*]) syntax rather than just x != y is because when doing a comparison to multiple values, any value can match, so != is like saying WHERE x != ANY(y[*]), which is not the intention.

db<>fiddle

Improve this answer
3
  • Thanks so much, this work fine using ids in hard code, do you know this package? github.com/porsager/postgres, if i use ${objetos} overriding ["123"] this show error not is a valid json object Commented Aug 2 at 20:10
  • No I'm sorry. It looks valid, I don't know why that shouldn't work. Does it support JSON arrays? Commented Aug 3 at 13:52
  • Thanks, this package accept, But I don't know how to use it Commented Aug 4 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.