Postgres 12

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 p_p_j (p_d_j jsonb);
insert into p_p_j
  values ('{
   "OID":"G3Q9Q0Z1547H0HS4",
   "cost":[
      {
         "spent":[
            {
               "ID":"HR",
               "spentamount":{
                  "amount":2000.0,
                  "country":"US"
               }
            }
         ]
      }
   ],
   "time":[
      {
         "spent":[
            {
               "ID":"HR",
               "spentamount":{
                  "amount":308.91,
                  "country":"US"
               }
            }
         ]
      }
   ]
}'::jsonb)
;

CREATE TABLE



INSERT 0 1


select e.item ->> 'ID' as id,
       (e.item #>> '{spentamount, amount}')::numeric as amount,
       e.item #>> '{spentamount, country}' as country
from p_p_j r
  cross join jsonb_each(r.p_d_j) as a(key, val)
  cross join lateral (
    select *
    from jsonb_array_elements(a.val) 
    where jsonb_typeof(a.val) = 'array'
  ) as s(element) 
  cross join jsonb_array_elements(s.element -> 'spent') as e(item)
;


id
amount
country


HR
2000.0
US

HR
308.91
US



SELECT 2


select e.item ->> 'ID' as id,
       (e.item #>> '{spentamount, amount}')::numeric as amount,
       e.item #>> '{spentamount, country}' as country
from p_p_j r
  cross join jsonb_path_query(r.p_d_j, '$.*[*].spent[*]') as e(item)


id
amount
country


HR
2000.0
US

HR
308.91
US



SELECT 2