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.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table user_table (id int, json_field jsonb);
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 into user_table values(1,'{
    "user": [
        {
            "user_name": "Devang",
            "user_weight": 0.7676846955248864
        },
        {
            "user_name": "Meet",
            "user_weight": 1.1021
        },
        {
            "user_name": "Devang",
            "user_weight": 0.16163873153859706
        },
        {
            "user_name": "Rajan",
            "user_weight": 0.22163873153859706
        }
    ],
    "address": [
        {
            "address_name": "India"
        }
    ]
}');
1 rows affected

1 rows affected

WITH cte AS (
  SELECT id, ('{user,'||index-1||'}')::text[] as json_path, (value->'user_weight')::text::numeric AS weight
  FROM user_table, jsonb_array_elements(json_field->'user')
  WITH ordinality arr(value, index) 
  WHERE arr.value->>'user_name' IN ('Devang', 'Meet') 
  ORDER BY id, value->'user_weight' DESC
) 
SELECT id,json_path,weight FROM (
  SELECT cte.*, 
    RANK() OVER (PARTITION BY id ORDER BY id,weight DESC
	             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r
  FROM cte) j
WHERE r > 1


id
json_path
weight


1
{user,0}
0.7676846955248864

1
{user,2}
0.16163873153859706

2
{user,1}
0.07447325861051013


select * from user_table


id
json_field


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"}]}

1
{"user": [{"user_name": "Devang", "user_weight": 0.7676846955248864}, {"user_name": "Meet", "user_weight": 1.1021}, {"user_name": "Devang", "user_weight": 0.16163873153859706}, {"user_name": "Rajan", "user_weight": 0.22163873153859706}], "address": [{"address_name": "India"}]}

id	json_path	weight
1	{user,0}	0.7676846955248864
1	{user,2}	0.16163873153859706
2	{user,1}	0.07447325861051013

id	json_field
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"}]}
1	{"user": [{"user_name": "Devang", "user_weight": 0.7676846955248864}, {"user_name": "Meet", "user_weight": 1.1021}, {"user_name": "Devang", "user_weight": 0.16163873153859706}, {"user_name": "Rajan", "user_weight": 0.22163873153859706}], "address": [{"address_name": "India"}]}