1

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 tSET 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 fiddlehttps://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?

askedDec 18, 2022 at 10:22
Maksym Popov's user avatar
3
  • Can you share the expected output?CommentedDec 18, 2022 at 11:21
  • 1
    updated post with expected resultCommentedDec 18, 2022 at 12:24
  • Use a proper, normalized data model, and the problem will go away automatically.CommentedDec 19, 2022 at 7:02

1 Answer1

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 finalWHERE condition to theUPDATE statement, can solve your problem.

UPDATE tSET 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) sWHERE t.id = s.id ;

Check the demohere.

answeredDec 18, 2022 at 13:18
lemon's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy 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.