Update field in array of objects in json postgres

Question

I have postgres database with next json in my table

{"jobTitle":"Technical Writer","distance":60,"skills":[{"name":"Bootstrap (Front-End Framework)","isPrimary":true,"type":""}, {"name":"Javascript","isPrimary":false,"type":""}]}

I need to update field "skills" depends on "isPrimary" value in each item there. If "isPrimary" false, update to 1, otherwise to 5.

I have next query

UPDATE t
SET body = jsonb_set(body::jsonb, '{skills}', s.skills_updated::jsonb)
FROM (
  SELECT
  jsonb_agg(
     jsonb_set(skills::jsonb, '{isPrimary}', 
        CASE
            WHEN skills ->> 'isPrimary' = 'true' THEN '5'::jsonb
            ELSE '1'::jsonb
        END
    )
  ) as skills_updated
  FROM t,
    json_array_elements(body -> 'skills') as skills
) s;

This query working incorrectly - it's accumulating all skills across all records in table and then setting this whole array in each record. this is my fiddle https://dbfiddle.uk/m28W7tPy

For instance, if I have next three records:

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": "true", "name": "Node.js" },{ "isPrimary": "false", "name": "Python" },{ "isPrimary": "false", "name": "Javascript" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": "true", "name": "Mysql" },{ "isPrimary": "false", "name": "Nest.js" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": "true", "name": "Postgres" },{ "isPrimary": "false", "name": "Typescript" }]}

I want to update just "isPrimary" for all above records and get in result next

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Node.js" },{ "isPrimary": 1, "name": "Python" },{ "isPrimary": 1, "name": "Javascript" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Mysql" },{ "isPrimary": 1, "name": "Nest.js" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Postgres" },{ "isPrimary": 1, "name": "Typescript" }]}

when I'm getting after running my query

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Node.js" },{ "isPrimary": 1, "name": "Python" },{ "isPrimary": 1, "name": "Javascript" }, { "isPrimary": 5, "name": "Mysql" },{ "isPrimary": 1, "name": "Nest.js" }, { "isPrimary": 5, "name": "Postgres" },{ "isPrimary": 1, "name": "Typescript" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Node.js" },{ "isPrimary": 1, "name": "Python" },{ "isPrimary": 1, "name": "Javascript" }, { "isPrimary": 5, "name": "Mysql" },{ "isPrimary": 1, "name": "Nest.js" }, { "isPrimary": 5, "name": "Postgres" },{ "isPrimary": 1, "name": "Typescript" }]}

{"jobTitle":"Writer","distance":60, "skills": [{ "isPrimary": 5, "name": "Node.js" },{ "isPrimary": 1, "name": "Python" },{ "isPrimary": 1, "name": "Javascript" }, { "isPrimary": 5, "name": "Mysql" },{ "isPrimary": 1, "name": "Nest.js" }, { "isPrimary": 5, "name": "Postgres" },{ "isPrimary": 1, "name": "Typescript" }]}

Can anyone help?

Use a proper, normalized data model, and the problem will go away automatically. — Laurenz Albe, Dec 19 '22 at 07:02

score 0 · Accepted Answer · answered Dec 18 '22 at 13:18

The issue with your query is that you're not keeping trace of each "id" value, nor you're matching it inside the update statement. Adding the "id" selection inside the subquery, and the final WHERE condition to the UPDATE statement, can solve your problem.

UPDATE t
SET body = jsonb_set(body::jsonb, '{skills}', s.skills_updated::jsonb)
FROM (
  SELECT id,
  jsonb_agg(
     jsonb_set(skills::jsonb, '{isPrimary}', 
        CASE
            WHEN skills ->> 'isPrimary' = 'true' THEN '5'::jsonb
            ELSE '1'::jsonb
        END
    )
  ) as skills_updated
  FROM t,
    json_array_elements(body -> 'skills') as skills
  GROUP BY id
) s
WHERE t.id = s.id ;

Check the demo here.

Update field in array of objects in json postgres

1 Answers1

Linked