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.
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:
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:
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';
