/SQL/Postgres multiple left join causing duplicates jsonb_agg

Postgres multiple left join causing duplicates jsonb_agg

SELECT tbl_a.id, tbl_a.name,
       COALESCE( ( SELECT json_agg(tbl_b.*)
                     FROM tbl_b 
                    WHERE tbl_b.a_id = tbl_a.id ), '{}'::json),
       COALESCE( ( SELECT json_agg(tbl_c.*)
                     FROM tbl_c 
                    WHERE tbl_c.a_id = tbl_a.id ), '{}'::json)
  FROM tbl_a;

Source: dba.stackexchange.com

Similar codes

12 votes197 views2 answers
Delete entries in postgresql
SQL
0 vote228 views1 answer
How to define a save method in ruby for sqlite3 databases
SQL
0 vote190 views1 answer
Run postgresql dump to csv
SQL
0 vote205 views1 answer
Sql server get date of previous sunday
SQL
0 vote182 views1 answer
Oracle drop default value
SQL
4 votes197 views2 answers
Sql offset
SQL
0 vote193 views1 answer
Postgres truncate with cascade
SQL
3 votes200 views2 answers
Sql asc
SQL
2 votes194 views2 answers
Mysql on duplicate key ignore
SQL
3 votes189 views2 answers
Sqlite show table definition
SQL