How do I retrieve data from multiple related tables in Postgres?

Question

Postgres 9.4
4 tables have many-to-many relations between each other. I've created additional table to implement relations:

CREATE TABLE "public"."relation" (
  "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "table1" uuid NOT NULL,
  "table2" uuid,
  "table3" uuid,
  "table4" uuid,
  "approved" bool DEFAULT true,
  CONSTRAINT "relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table1" FOREIGN KEY ("table1") REFERENCES "public"."table1" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table2" FOREIGN KEY ("table2") REFERENCES "public"."table2" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table3" FOREIGN KEY ("table3") REFERENCES "public"."table3" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table4" FOREIGN KEY ("table4") REFERENCES "public"."table4" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."relation" OWNER TO "postgres";

I need to retrieve a single row from table1 including all the related rows from other tables as JSON object. Here i've retrieved rows from relation table:

SELECT t.*
   FROM ( SELECT table1.id,
            (select row_to_json(relations.*) as array_to_json
              from(select * from relation where table1 = table1.id) relations
            ) as relations,

           from public.table1) t

But I can't figure out how to effectively retrieve rows from related tables through the data from the relation table.

May be this is valuable information:
Each row in relation table contains only two relations. For example, it might contain relation to table1 and table2. The rest of the columns are empty (except id, of course). Each row from tables table1,2,3,4 has less than 10 relations.
I want to retrieve something like this:

{
    id: table1.id,
    name: table1.name,
    related_items: [
        {id: table2.id, name: table2.name},
        {id: table4.id, name: table4.name},
        {id: table3.id, name: table3.name},
        {id: table2.id, name: table2.name},
        {id: table3.id, name: table3.name},
    ]
}

Thank you for your time!

do you want to 1. extract the data from all the 4 related tables using the relations table, 2. all on one record and then 3. get the equivalent JSON by using the row_to_json function? — vmachan, Dec 23 '15 at 17:31
Try this post - http://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins. It contains a lot of useful info on what you are trying to do — vmachan, Dec 23 '15 at 17:50

Christian · Answer 1 · 2015-12-23T19:49:32.193

SELECT ROW_TO_JSON(T)
FROM    (
        SELECT t1.id,
               t1.name,
               array_to_json(array((
                 select case when t2.id is not null then row_to_json(t2) 
                             when t3.id is not null then row_to_json(t3) 
                             when t4.id is not null then row_to_json(t4) 
                        end
                 from   public.relation r 
                 LEFT JOIN public.table1 t2 on r.table2 = t2.id
                 LEFT JOIN public.table1 t3 on r.table3 = t3.id
                 LEFT JOIN public.table1 t4 on r.table4 = t4.id
                 where  r.table1 = t1.id
               ))) related_items
        FROM   public.table1 t1
       ) T

How do I retrieve data from multiple related tables in Postgres?

1 Answers1

Linked