using SUM on foreign view's keys grouped on multiple key/values? I had a question on executing SUM on rows from one table and inserting them in a column on another table where the calculated rows shared id with a row in the new table.

The solution worked very well for that instance. However, I have a similar situation in which that query no longer works.

I have this TABLE:

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);


then this VIEW:

CREATE VIEW v_rooms AS  
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
    jsonb_array_elements( document -&gt; 'levels' ) -&gt; 'meta' -&gt;&gt; 'level_number' AS level_number,
    ((jsonb_array_elements(jsonb_array_elements(buildings.document -&gt; 'levels'::text) -&gt; 'rooms'::text) -&gt; 'meta'::text) -&gt;&gt; 'sqft'::text)::numeric AS sqft
FROM buildings
GROUP BY buildings.id;


finally in this VIEW:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -&gt; 'levels' ) -&gt; 'meta' -&gt;&gt; 'level_number' AS number,
  sqft
FROM buildings


I want the COLUMN 'sqft' to be the SUM of all rows in v_rooms where v_rooms.building_id = v_levels.building_id AND v_rooms.level_number = v_levels.number.

I tried this VIEW definition:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -&gt; 'levels' ) -&gt; 'meta' -&gt;&gt; 'level_number' AS number,
  sqft
FROM buildings
JOIN (
  SELECT building_id, level_number, sum(sqft) as sqft
  FROM v_rooms
  GROUP BY ( v_rooms.building_id, v_rooms.level_number )
) v_rooms ON ( v_rooms.building_id = buildings.id and v_rooms.level_number = level_number );


but I get duplicates, one row for the matching building_id and then again every time level number matches. which is an issue because level number is not unique to the table and only to the building.

FIDDLE
You barely got it, once you have unnested the arrays you can simply aggregate the result by building.id, name and level in this way:

WITH ct AS
(
  SELECT 
    buildings.id AS building_id
    ,buildings.name AS building_name
    ,jsonb_array_elements( document -&gt; 'levels' ) -&gt; 'meta' -&gt;&gt; 'level_number' AS level_number
    ,((jsonb_array_elements(jsonb_array_elements(buildings.document-&gt;'levels')-&gt;'rooms')-&gt;'meta')-&gt;&gt;'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



db&lt;>fiddle here