CODEWITHSUNDEEP
sqlpostgresql
stkvtflw
stkvtflw

Reputation: 13577

How do I SELECT data from child table in PostgreSQL?

In PostgreSQL 9.4, how do I retrieve json object like this:

parentTableFirstProp: 'string',
parentToChildReference: [
    {childTableFirstProp: 'another string'},
    {childTableFirstProp: 'yet another string'}
}]

instead of this:

[{
    parentTableFirstProp: 'string',
    childTableFirstProp: 'another string',
},{
    parentTableFirstProp: 'string',
    childTableFirstProp: 'yet another string'
}]

Do I always have to make 2 select queries, and insert one to another by usage of aliases? Could you just show an example: how do you SELECT parent table along with it's children?

UPD 1 this:

SELECT
"public"."ParentTable".*,
"public"."ChildTable".*
FROM
"public"."ParentTable"
RIGHT JOIN "public"."ChildTable"
ON "public"."ParentTable"."childReference"

returns this:

[{
        parentTableFirstProp: 'string',
        childTableFirstProp: 'another string',
    },{
        parentTableFirstProp: 'string',
        childTableFirstProp: 'yet another string'
    }]

UPD 2
Create tables statements:

CREATE TABLE "public"."ParentTable" (
    "id" varchar(36) NOT NULL COLLATE "default",
    "parentTableFirstProp" varchar(100) NOT NULL COLLATE "default",
    "parentToChildReference" varchar COLLATE "default"
)

CREATE TABLE "public"."ChildTable" (
    "id" varchar(36) NOT NULL COLLATE "default"
    "childTableFirstProp" varchar(100) NOT NULL COLLATE "default",
)

Upvotes: 1

Views: 1091

Answers (1)

Dan Sin
Dan Sin

Reputation: 491

A new feature in PostgreSQL 9.2 but I didn't test the query:

I follow the tutorial from here.

select row_to_json(t)
from (
select ParentTable.parentTableFirstProp, (
select array_to_json(array_agg(row_to_json(child)))
  from (
    select childTableFirstProp
    from ChildTable
    where ChildTable.id=ParentTable.parentToChildReference
  ) child

  ) as parentToChildReference
from ParentTable
) t

Upvotes: 2

Related Questions