0

I have a jsonb field that contained the something like below: How to update is_read properties in extras node to true where the users_pid = 1 and is_read=false?

I have tried below:

UPDATE chats 
   SET attributes = jsonb_set(
                              cast(attributes->'data'->>'extras' AS jsonb),
                              array['is_read'], 
                              to_jsonb(true)
                             )
 WHERE users_pid =1 
   AND cast(attributes->'data'->'extras_to'- >>'is_read' AS boolean) = false

but nothing updated

[
 {
   "data": {
   "users_pid": 1,
   "datetime": "2022-05-01 13:10:58",
   "extras": {
     "is_read": false,
     "read_dt": ""
    }
   }
 },
 
 {
    "data": {
    "users_pid": 3,
    "datetime": "2022-05-23 11:03:22",
    "extras": {
       "is_read": false,
       "read_dt": ""
     }
   }
 },
 {
   "data": {
     "users_pid": 1,
     "datetime": "2022-05-13 11:23:22",
     "extras": {
     "is_read": false,
     "read_dt": ""
    }
  }
 }
]
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Dev
  • 107
  • 6
  • Does this answer your question? [How do I modify fields inside the new PostgreSQL JSON datatype?](https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) – Julia May 24 '22 at 06:26
  • sorry @a_horse_with_no_name PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit – Dev May 24 '22 at 07:06

0 Answers0