Postgres 9.5

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?.
create table table_(id int PRIMARY KEY, value_ jsonb);
insert into table_ values (1, '{
    "iProps": [
    {
        "value": {
            "rules": [
                {
                "name": "PRule",
                "ao": {
                        "set": "get"
                       }
                },
                {
                    "ao": {
                        "sc": {
                            "link": "name1.com",
                            "name": "name1"
                        }
                    }
                },
                {
                    "ao": {
                        "sc": {
                            "link": "name2.com",
                            "name": "name2"
                        }
                    }
                },
                {
                    "ao": {
                        "sc": {
                            "link": "name3.com",
                            "name": "name3"
                        }
                    }
                }
            ]
        }
    }
]
}')
1 rows affected

 select id, jsonb_agg(sub1.new_rules)
      from
      
      (SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      where rule #> '{ao,sc}' is not null
      union all
           SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao}', rule #> '{ao}', false)
                         AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
       where rule #> '{ao,sc}' is  null) sub1
       group by id
       


id
jsonb_agg


1
[{"ao": {"sc": "name1"}}, {"ao": {"sc": "name2"}}, {"ao": {"sc": "name3"}}, {"ao": {"set": "get"}, "name": "PRule"}]


select jsonb_set(sub2.prop, '{value, rules}', sub2.agg_rules, false) AS iProps
FROM
(select id, jsonb_agg(sub1.new_rules) AS agg_rules, prop
      from
      
      (SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      where rule #> '{ao,sc}' is not null
      union all
           SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao}', rule #> '{ao}', false)
                         AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
       where rule #> '{ao,sc}' is  null) 
       sub1
       group by id, prop) 
       sub2


iprops


{"value": {"rules": [{"ao": {"sc": "name1"}}, {"ao": {"sc": "name2"}}, {"ao": {"sc": "name3"}}, {"ao": {"set": "get"}, "name": "PRule"}]}}


UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', iProps, false)
FROM  (select jsonb_set(sub2.prop, '{value, rules}', sub2.agg_rules, false) AS iProps
FROM
(select id, jsonb_agg(sub1.new_rules) AS agg_rules, prop
      from
      
      (SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      where rule #> '{ao,sc}' is not null
      union all
           SELECT t.id, (arr1.prop), arr1.idx1,
jsonb_set(rule, '{ao}', rule #> '{ao}', false)
                         AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
       where rule #> '{ao,sc}' is  null) 
       sub1
       group by id, prop) 
       sub2) sub3
1 rows affected

select jsonb_pretty(value_) from table_;


jsonb_pretty


{
    "iProps": {
        "value": {
            "rules": [
                {
                    "ao": {
                        "sc": "name1"
                    }
                },
                {
                    "ao": {
                        "sc": "name2"
                    }
                },
                {
                    "ao": {
                        "sc": "name3"
                    }
                },
                {
                    "ao": {
                        "set": "get"
                    },
                    "name": "PRule"
                }
            ]
        }
    }
}