I need to update id=2 when id=46 - probably using CASE expression as I have multiple objects with different values.

select q.a from (select json_array_elements_text('[{"id":46,"value":"a"},{"id":35,"value":"b"}]'::json) as a)q.

Also, why do I get the error "operator does not exist: text ->> unknown" when trying to run:

select q.a from (select  json_array_elements_text('[{"id":46,"value":"a"}]'::json) as a)q where a->>'id'=46

Just want to understand what I am missing.

CodePudding user response：

For Postgres 12 or later, and jsonb (not json):

UPDATE tbl
SET    a = (
   SELECT jsonb_agg(CASE WHEN obj->>'id' = '46'
                         THEN jsonb_set(obj, '{id}', '2', false)
                         ELSE obj END)
   FROM   jsonb_array_elements(a) obj
   )
WHERE  a @? '$.id ? (@ == 46)';

See:

• Update all values for given key nested in JSON array of objects

why do I get the error "operator does not exist: text ->> unknown" ...

As the name of the function json_array_elements_text() hints, it returns type text. Use json_array_elements() instead for your purpose. See:

• How to turn JSON array into Postgres array?

Also, a ->> 'id' returns text, so you can't compare it to an integer. This works:

SELECT q.a
FROM   json_array_elements('[{"id":46,"value":"a"}]'::json) AS q(a)
WHERE  a ->> 'id' = '46';