Group a record under a static type using sql

user_3840211 0
Viewed 54

StudentType

ID Name
1 Hostel
2 Home

Students

ID Name studentTypeId deletedAt
1 First 1 null.
2 Second 1 2022-02-07
3 Third null.
4 Fourth 2 null
5 Fifth 2 null

So, I have two tables studentTypes and students. The student table could exist without the studentTypeId.

Using sql I want to be able to get the following json. The student with no id needs to be under unassigned type.

{
"studentTypes": [{
        "id": 1,
        "name": "Hostel",
        "students": [{
                "id": 1,
                "name": "First",
                "studentTypeId": 1
            }
        ]
    },
    {
        "id": 2,
        "name": "Home Going",
        "students": [{
                "id": 1,
                "name": "Fourth",
                "studentTypeId": 2
            },
            {
                "id": 1,
                "name": "Fifth",
                "studentTypeId": 2
            }
        ]
    },
    {
        "name": "Unassigned",
        "students": [{
            "id": 3,
            "name": "Third",
            "studentTypeId": null
        },
        {
            "id": 1,
            "name": "Second",
            "studentTypeId": 1
        }]
    }
]

}

I want the last type as Unassigned which will have students with no studentTypeId as well as which has deletedAt set. I cant figure-out how to get it.

postgresql sql json
1 Answers

The Unassigned student types can be included via a UNION ALL.
Then the students can be joined lateral via an IS NOT DISTINCT FROM.

If you want a pretty json, you could use the jsonb_pretty function.

WITH CTE_STUDENTTYPES AS (
  SELECT id, name
  FROM StudentType
  UNION ALL
  SELECT NULL, 'Unassigned'
)
SELECT 
JSON_BUILD_OBJECT ('studentTypes', 
JSON_AGG (
  CASE WHEN t.ID IS NULL
  THEN JSON_BUILD_OBJECT ('name', t.name, 'students', l.Students)
  ELSE JSON_BUILD_OBJECT ('id', t.ID, 'name', t.name, 'students', l.Students)
  END
))::json as StudentTypes
FROM CTE_STUDENTTYPES t
CROSS JOIN LATERAL (
  SELECT JSON_AGG (
       JSON_BUILD_OBJECT ('id', s.ID, 'name', s.name, 'studentTypeId', s.studentTypeId)
  ) AS Students
  FROM Students s
  WHERE (CASE WHEN s.deletedAt IS NULL
         THEN s.studentTypeId 
         END) IS NOT DISTINCT FROM t.ID
) l;
studenttypes
{"studentTypes" : [{"id" : 1, "name" : "Hostel", "students" : [{"id" : 1, "name" : "First", "studentTypeId" : 1}]}, {"id" : 2, "name" : "Home", "students" : [{"id" : 4, "name" : "Fourth", "studentTypeId" : 2}, {"id" : 5, "name" : "Fifth", "studentTypeId" : 2}]}, {"name" : "Unassigned", "students" : [{"id" : 3, "name" : "Third", "studentTypeId" : null}, {"id" : 2, "name" : "Second", "studentTypeId" : 1}]}]}

Demo on db<>fiddle here

user_40034190
Related