Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


create table user_table (id int, json_field jsonb);

CREATE TABLE


insert into user_table values(2,'{
    "user": [
        {
            "user_name": "Devang",
            "user_weight": 0.7676846955248864
        },
        {
            "user_name": "Meet",
            "user_weight": 0.07447325861051013
        },
        {
            "user_name": "L.b.vasoya",
            "user_weight": 0.056163873153859706
        }
    ],
    "address": [
        {
            "address_name": "India"
        }
    ]
}');


INSERT 0 1


with cte as (
  select  id,  ('{user,'||index-1||',user_name}')::text[] as json_path
  from user_table, jsonb_array_elements(json_field->'user') 
  with ordinality arr(vals,index) where arr.vals->>'user_name' ='Devang'
  )

 update user_table 
set json_field = jsonb_set(json_field,cte.json_path,'"Dev"',false) 
 from cte where user_table.id=cte.id;
  

UPDATE 1


select * from user_table


id
json_field


2
{"user": [{"user_name": "Dev", "user_weight": 0.7676846955248864}, {"user_name": "Meet", "user_weight": 0.07447325861051013}, {"user_name": "L.b.vasoya", "user_weight": 0.056163873153859706}], "address": [{"address_name": "India"}]}



SELECT 1