Postgres 16

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 private_notion(site_id uuid,record_map jsonb);
insert into private_notion values
('45bf37be-ca0a-45eb-838b-015c7a89d47b',
  '{
  "blocks": {
    "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
      "name": "block1",
      "value": 1,
      "child": {
        "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
          "name": "block2",
          "value": 2,
          "child": {
            "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
              "name": "block3",
              "value": 3
            }
          }
        },
        "7a9abf0d-a066-4466-a565-4e6d7a960a38": {
          "name": "block4",
          "value": 4,
          "child": {
            "7a9abf0d-a066-4466-a565-4e6d7a960a39": {
              "name": "block5",
              "value": 5,
              "child": {
                "7a9abf0d-a066-4466-a565-4e6d7a960a40": {
                  "name": "block6",
                  "value": 6
                }
              }
            }
          }
        }
      }
    }
  }
}')
returning site_id,jsonb_pretty(record_map);

CREATE TABLE




site_id
jsonb_pretty


45bf37be-ca0a-45eb-838b-015c7a89d47b
{
    "blocks": {
        "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
            "name": "block1",
            "child": {
                "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
                    "name": "block2",
                    "child": {
                        "7a9abf0d-a066-4466-a565-4e6d7a960a37": {
                            "name": "block3",
                            "value": 3
                        }
                    },
                    "value": 2
                },
                "7a9abf0d-a066-4466-a565-4e6d7a960a38": {
                    "name": "block4",
                    "child": {
                        "7a9abf0d-a066-4466-a565-4e6d7a960a39": {
                            "name": "block5",
                            "child": {
                                "7a9abf0d-a066-4466-a565-4e6d7a960a40": {
                                    "name": "block6",
                                    "value": 6
                                }
                            },
                            "value": 5
                        }
                    },
                    "value": 4
                }
            },
            "value": 1
        }
    }
}



INSERT 0 1


SELECT jsonb_path_query(record_map, 
                        $.**.7a9abf0d-a066-4466-a565-4e6d7a960a37.name)
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';

ERROR:  syntax error at or near "$"
LINE 2:                         $.**.7a9abf0d-a066-4466-a565-4e6d7a9...
                                ^


SELECT jsonb_path_query(record_map, 
                        '$.**.7a9abf0d-a066-4466-a565-4e6d7a960a37.name')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';

ERROR:  trailing junk after numeric literal at or near ".7a" of jsonpath input
LINE 2:                         '$.**.7a9abf0d-a066-4466-a565-4e6d7a...
                                ^


SELECT jsonb_path_query(record_map, 
                        'strict $.**."7a9abf0d-a066-4466-a565-4e6d7a960a37".name')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


"block1"

"block2"

"block3"



SELECT 3


SELECT jsonb_path_query(record_map, 
                        '$.**?(exists(@."7a9abf0d-a066-4466-a565-4e6d7a960a37")).*.name')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


"block1"

"block2"

"block4"

"block3"



SELECT 4


SELECT jsonb_path_query(record_map, 
                        'strict $.**."7a9abf0d-a066-4466-a565-4e6d7a960a37"')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


{"name": "block1", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block2", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block3", "value": 3}}, "value": 2}, "7a9abf0d-a066-4466-a565-4e6d7a960a38": {"name": "block4", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a39": {"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}}, "value": 4}}, "value": 1}

{"name": "block2", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block3", "value": 3}}, "value": 2}

{"name": "block3", "value": 3}



SELECT 3


SELECT jsonb_path_query(record_map, 
                        'strict $.**."7a9abf0d-a066-4466-a565-4e6d7a960a39"')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


{"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}



SELECT 1


SELECT jsonb_path_query(record_map, 
                        'strict $.**?(exists(@."7a9abf0d-a066-4466-a565-4e6d7a960a39"))')
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


{"7a9abf0d-a066-4466-a565-4e6d7a960a39": {"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}}



SELECT 1


SELECT jsonb_path_query(record_map, 
                        'strict $.**?(@.keyvalue().key==$target_id)',
                        jsonb_build_object('target_id',
                                           '7a9abf0d-a066-4466-a565-4e6d7a960a37'))
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


{"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block1", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block2", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block3", "value": 3}}, "value": 2}, "7a9abf0d-a066-4466-a565-4e6d7a960a38": {"name": "block4", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a39": {"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}}, "value": 4}}, "value": 1}}

{"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block2", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block3", "value": 3}}, "value": 2}, "7a9abf0d-a066-4466-a565-4e6d7a960a38": {"name": "block4", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a39": {"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}}, "value": 4}}

{"7a9abf0d-a066-4466-a565-4e6d7a960a37": {"name": "block3", "value": 3}}



SELECT 3


SELECT jsonb_path_query(record_map, 
                        'strict $.**?(@.keyvalue().key==$target_id).*',
                        jsonb_build_object('target_id',
                                           '7a9abf0d-a066-4466-a565-4e6d7a960a39'))
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';


jsonb_path_query


{"name": "block5", "child": {"7a9abf0d-a066-4466-a565-4e6d7a960a40": {"name": "block6", "value": 6}}, "value": 5}



SELECT 1