CREATE TABLE parents (
  parent_id INTEGER PRIMARY KEY,
  name VARCHAR
);

CREATE TABLE children (
  child_id INTEGER PRIMARY KEY,
  name VARCHAR,
  birthdate DATE,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES parents
);


INSERT INTO parents VALUES
    (1, 'Jim')
  , (2, 'Jenny')
  ;

INSERT INTO children VALUES
    (1, 'Tamara', '2017-02-01', 1)
  , (2, 'Tim', '2000-11-02', 2)
  , (3, 'Tom', '2005-10-01', 1)
  , (4, 'Theresa', '2017-04-30', 2)
  , (5, 'Tonja', '2011-07-17', 1)
  ;


SELECT * FROM parents JOIN children USING (parent_id) ORDER BY parent_id;

SELECT
    parent_id
  , p.name
  , array_agg(json_build_object(
      'child_id', c.child_id
    , 'name', c.name
    , 'birthdate', c.birthdate
  )) as children
FROM parents p
JOIN children c USING (parent_id)
GROUP BY 1, 2;


explain analyze SELECT * FROM parents JOIN children USING (parent_id) ORDER BY parent_id;

explain analyze SELECT
    parent_id
  , p.name
  , array_agg(json_build_object(
      'child_id', c.child_id
    , 'name', c.name
    , 'birthdate', c.birthdate
  )) as children
FROM parents p
JOIN children c USING (parent_id)
GROUP BY 1, 2;
