Postgres 11

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 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE foods (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE nutrients (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE foods_nutrients (
  food_id int REFERENCES foods(id) ON UPDATE CASCADE ON DELETE CASCADE,
  nutrient_id int REFERENCES nutrients(id) ON UPDATE CASCADE ON DELETE CASCADE,
  amount DECIMAL NOT NULL,
  CONSTRAINT food_nutrient_pk PRIMARY KEY (food_id, nutrient_id)
);

INSERT INTO foods(name)
VALUES  ('Apple'),
        ('Banana');
INSERT INTO nutrients(name)
VALUES  ('Carbohydrates'),
        ('Protein'),
        ('Fat');
INSERT INTO foods_nutrients(food_id, nutrient_id, amount)
VALUES  (1, 1, 14.0),
        (1, 2, 0.3),
        (1, 3, 0.2),
        (2, 1, 23.7),
        (2, 2, 1.1);
2 rows affected

3 rows affected

5 rows affected

WITH fn1 AS
(
SELECT fn.food_id, f.name,
       JSON_OBJECT_AGG( nutrient_id, amount ) AS nutrients                        
  FROM foods_nutrients fn 
  JOIN foods f 
    ON fn.food_id=f.id
  JOIN nutrients n 
    ON fn.nutrient_id=n.id
 GROUP BY fn.food_id, f.name  
)
SELECT JSON_AGG(
                JSON_BUILD_OBJECT( 'id', food_id, 
                                   'name', name,
                                    'nutrients', nutrients) 
                         ) AS js                
  FROM fn1


js


[{"id" : 1, "name" : "Apple", "nutrients" : { "1" : 14.0, "2" : 0.3, "3" : 0.2 }}, {"id" : 2, "name" : "Banana", "nutrients" : { "1" : 23.7, "2" : 1.1 }}]