Nested Join with aggregation in posgres

Question

In my DB, there are two tables

EventType
- ID (Primary key)
- Name
ActivityType
- ID (Primary key)
- Name
- EventTypeID (foreign key)
- ParentActivityTypeID (Relation with self ID)

I have tried with the following query to aggregate the json

SELECT  coalesce(json_build_object(
        'EventTypeID', ev."ID",
        'EventTypeName', ev."Name",
        'ActivityType', json_agg(json_build_object('ID',ac."ID",'Name',ac."Name",'ParentActivityType',json_agg(select * from "Activity" where ))
    ), '{}'::json)  AS item
FROM  "EventType" as ev
JOIN  "ActivityType" as ac ON ev."ID" = ac."EventTypeID"
GROUP  BY ev."ID"

expected JSON output

[{
  "EventTypeID": 2,
  "EventTypeName": "On-Site Care",
  "ActivityType": [
    {
      "ID": 1,
      "Name": "Measurement",
      "EventTypeID": 2,
      "ParentActivityTypeID": null,
      "SubActivityType": [
        {
          "ID": 17,
          "Name": "abc",
          "EventTypeID": 2,
          "ParentActivityTypeID": 1
        }
      ]
    },
    {
      "ID": 2,
      "Name": "Medication",
      "EventTypeID": 2,
      "ParentActivityTypeID": null
    },
    {
      "ID": 3,
      "Name": "Wellness check",
      "EventTypeID": 2,
      "ParentActivityTypeID": null
    },
    {
      "ID": 4,
      "Name": "Other",
      "EventTypeID": 2,
      "ParentActivityTypeID": null
    }
  ]
},
{
  "EventTypeID": 3,
  "EventTypeName": "Care Call",
  "ActivityType": [
    {
      "ID": 1,
      "Name": "Measurement",
      "EventTypeID": 3,
      "ParentActivityTypeID": null,
      "SubActivityType": [
        {
          "ID": 17,
          "Name": "abc",
          "EventTypeID": 3,
          "ParentActivityTypeID": 1
        }
      ]
    },
    {
      "ID": 2,
      "Name": "Medication",
      "EventTypeID": 3,
      "ParentActivityTypeID": null
    },
    {
      "ID": 3,
      "Name": "Wellness check",
      "EventTypeID": 3,
      "ParentActivityTypeID": null
    },
    {
      "ID": 4,
      "Name": "Other",
      "EventTypeID": 3,
      "ParentActivityTypeID": null
    }
  ]
}
]

Pratik Soni · Accepted Answer · 2019-08-09 15:18:53Z

1

You can join self table as parent described as below.

SELECT  coalesce(json_build_object(
        'EventTypeID', ev."ID",
        'EventTypeName', ev."Name",
        'ActivityType', json_agg(json_build_object('ID',ac."ID",'Name',ac."Name",'ParentActivityType',json_agg(parent.*))
    ), '{}'::json)  AS item
FROM  "EventType" as ev
LEFT JOIN  "EventType" as parent ON ev."ParentActivityTypeID" = parent."ID"
JOIN  "ActivityType" as ac ON ev."ID" = ac."EventTypeID"
GROUP  BY ev."ID"

answered Aug 9, 2019 at 15:18

Pratik Soni

2,57618 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested Join with aggregation in posgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related