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 buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);
INSERT INTO buildings ( name, document )
VALUES (
 'my blding','{"levels": [{"meta": 
     {"level_number": 0},
          "rooms": [
          {"meta": {"sqft": 500}}, 
          {"meta": {"sqft": 800}}]},
          {"meta": {
      "level_number": 1},
          "rooms": [
          {"meta": {"sqft": 900}}, 
          {"meta": {"sqft": 900}}]}]}')
1 rows affected

SELECT 
    buildings.id AS building_id
    ,buildings.name AS building_name
    ,jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number
    ,((jsonb_array_elements(jsonb_array_elements(buildings.document->'levels')->'rooms')->'meta')->>'sqft')::numeric AS sqft
FROM 
    buildings;


building_id
building_name
level_number
sqft


1
my blding
0
500

1
my blding
0
800

1
my blding
1
900

1
my blding
1
900


WITH ct AS
(
  SELECT 
    buildings.id AS building_id
    ,buildings.name AS building_name
    ,jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number
    ,((jsonb_array_elements(jsonb_array_elements(buildings.document->'levels')->'rooms')->'meta')->>'sqft')::numeric AS sqft
  FROM 
    buildings
)
SELECT
    building_id, building_name, level_number,
    SUM(sqft) AS sqft
FROM
    ct
GROUP BY
    building_id, building_name, level_number;


building_id
building_name
level_number
sqft


1
my blding
0
1300

1
my blding
1
1800