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
– Laurenz Albe, Commented Dec 19, 2022 at 7:02

lemon · Accepted Answer · 2022-12-18 13:18:49Z

0

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.

answered Dec 18, 2022 at 13:18

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update field in array of objects in json postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related