Postgres 15

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 your_table(
  id int generated by default as identity primary key,
  data jsonb);
insert into your_table select * from jsonb_populate_recordset(null::your_table,
'[{
  "id": 1,
  "data": {
    "prices": {
      "x": {
         "price": 20
      }
    }
  }
},
{
  "id": 2,
  "data": {
    "prices": {
      "y": {
         "price": 86
      }
    }
  }
},
{
  "id": 3,
  "data": {
    "prices": {
      "z": {
         "price": 21
      },
      "b": {
         "price": 41
      }
    }
  }
}]')
returning id,jsonb_pretty(data);

CREATE TABLE




id
jsonb_pretty


1
{
    "prices": {
        "x": {
            "price": 20
        }
    }
}

2
{
    "prices": {
        "y": {
            "price": 86
        }
    }
}

3
{
    "prices": {
        "b": {
            "price": 41
        },
        "z": {
            "price": 21
        }
    }
}



INSERT 0 3


select id,price from 
  (select id,price,row_number()over(partition by id order by price desc)
   from your_table 
   cross join lateral jsonb_path_query(data,'$.prices.*.price')as price
   where data @? '$.prices.*.price')_ 
where row_number=1
order by price desc;


id
price


2
86

3
41

1
20



SELECT 3


select id,price from 
  (select distinct on(id)id,price
   from your_table 
   cross join lateral jsonb_path_query(data,'$.prices.*.price')as price
   where data @? '$.prices.*.price'
   order by id,price desc)_ 
order by price desc;


id
price


2
86

3
41

1
20



SELECT 3


select jsonb_pretty(jsonb_agg(to_jsonb(subquery)order by price desc)) 
from(select distinct on(id)id,price
     from your_table 
     cross join lateral jsonb_path_query(data,'$.prices.*.price')as price
     where data @? '$.prices.*.price'
     order by id,price desc)as subquery;


jsonb_pretty


[
    {
        "id": 2,
        "price": 86
    },
    {
        "id": 3,
        "price": 41
    },
    {
        "id": 1,
        "price": 20
    }
]



SELECT 1


/*would return

[
  {
    id: 2,
    price: 86
  },
  {
    id: 3,
    price: 41
  },
  {
    id: 1,
    price: 20
  }
]*/

id	jsonb_pretty
1	{ "prices": { "x": { "price": 20 } } }
2	{ "prices": { "y": { "price": 86 } } }
3	{ "prices": { "b": { "price": 41 }, "z": { "price": 21 } } }