Update specified data in PostgreSQL/ JSON based on 2 conditions

Question

I searched a lot to find solution and I found something similar but unfortunately I couldn't apply it to my case, maybe because I am yet new to Postgres. The link to this similar issue is here: Update a JSON column with WHERE Clause in JSON Column Postgres

I would like to update a specific data inside Postgres/ JSON.

  {
   "name": "wewgrg",
   "path": "fitnessappmaven\\src\\main\\java\\com\\klaro\\fitnessappmaven\\tempWorkoutIcons\\yoga.png",
   "type": "Gym"
  },
  {
   "name": "rhhrh",
   "path": "fitnessappmaven\\src\\main\\java\\com\\klaro\\fitnessappmaven\\tempWorkoutIcons\\yoga.png",
   "type": "Cardio"
  },

This is the JSON structure I would like to modify. The name indicates the name of the workout. What I'd like to do is to update "name" where "name" is "wewgrg" and where the "username" column is Jon Doe(for example).

I tried something like this, but obviously the second where clause doesn't work.

UPDATE my_users SET json_workouts = JSONB_SET(json_workouts,'{name}','\"LOL\"') WHERE username='c' AND '{name}' = 'wewgrg';

So as far as I know this query works until the first WHERE clause and I don't know how to specify it further by the "name" property itself.

I hope I wrote it understandable. Thank you for any help in advance!

P.S.: I got answer previously regarding to storing/updating JOSN data in Postgres, that it would be easier to use properly normalized data. I have no idea what is that, if you suggest it, please provide example! Thank you :)

Answer 1

Judging from your question, your current table looks something like:

CREATE TABLE my_users
( username text not null primary key
, json_workouts jsonb not null
, ...
);

Though you can update parts of a string or even bits in an integer, the idea is that the content in a column is supposed to be atomic, you should not normally update parts of it. In a sence you are replacing the content of the column with a modified copy.

If your json structure is as static as you indicate in your question, you could normalize it as:

CREATE TABLE my_users
( username TEXT NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE my_users_workouts
( username TEXT NOT NULL
, workout_name TEXT NOT NULL
, workout_type TEXT NOT NULL
, workout_path TEXT NOT NULL
,    PRIMARY KEY (username, workout_name)
,    FOREIGN KEY (username) REFERENCES my_users (username)
);

In such model, your update query would be:

UPDATE my_users_workouts
    SET workout_name = 'LOL'
WHERE username = 'c'
  AND workout_name = 'wewgrg'

Since workout_name in a sense is atomic, it is much easier to update it.

Of course I'm just inventing things here because I don't know enough of your requirements to properly model it, but it should give you an idea of what is meant by "use properly normalized data".