I am trying to create a SQL View called 'Inpatients' to simplify my database queries, this has worked well for the key one-to-one data I have in my database but I am now struggling to accomodate my one-to-many. I am using Ruby on Rails with the Scenic gem and a PostgreSQL database (but would like to be able to accomodate SQL Server in the future).

Specifically, I have a list of patients who can have multiple tasks. I have managed to link the tasks and place them into a JSON object but am getting multiple rows for each patient (equal to the number of tasks).

This is how I am building by column currently:

jsonb_build_object(
    'id', Tasks.id, 'title', Tasks.title, 'completed', Tasks.completed
    ) as tasks

How do I aggregate these tasks into an array of json objects (of all tasks), rather than multiple rows each with a single JSON object for a single tasks?

I have tried various commands from elsewhere on Stack Overflow and elsewhere but can't work out what I am doing wrong (GROUP_BY, json_agg. array_to_json, nested SQL Select statements). Ideally I would have an agnostic solution (I am currently using Postgres but would like to support SQL Server).

Any help would be greatly appreciated!

Screenshot - this shows the output when I display the results, where the final column is tasks which I would like to have as a single array of json objects. Details are randomly generated and not indicative of a real patient.

SQL View - this is the full SQL statement I have created

SELECT

Encounters.id as encounter_id,
Encounters.start, Encounters.end, Encounters.estimated_end,

Patients.id as patient_id,
Patients.healthid, Patients.localid, Patients.governmentid, Patients.hexeid,
Patients.given_names, Patients.last_name, Patients.gender, Patients.admin_gender,
Patients.birth_date, Patients.birth_time, Patients.deceased,

Beds.id as bed_id,
Beds.number, Beds.bay, Beds.cubicle, Beds.level, Beds.clean,

Locations.id as location_id,
Locations.title,

jsonb_build_object(
    'id', Tasks.id, 'title', Tasks.title, 'completed', Tasks.completed
    ) as tasks

FROM Encounters
INNER JOIN Patients ON Encounters.patient_id=Patients.id
INNER JOIN Beds ON Encounters.bed_id=Beds.id
INNER JOIN Locations ON Beds.location_id=Locations.id
INNER JOIN Tasks ON Patients.id=Tasks.patient_id

WHERE Encounters.end IS null

CodePudding user response：

You can aggregate the tasks in a derived table:

SELECT encounters.id AS encounter_id,
       encounters.start, encounters.end, encounters.estimated_end,
       patients.id as patient_id,
       patients.healthid, patients.localid, patients.governmentid, patients.hexeid,
       patients.given_names, patients.last_name, patients.gender, patients.admin_gender,
       patients.birth_date, patients.birth_time, patients.deceased,

       beds.id as bed_id,
       beds.number, beds.bay, beds.cubicle, beds.level, beds.clean,

       locations.id as location_id,
       locations.title,
       
       tsk.all_tasks
FROM encounters
  JOIN patients ON encounters.patient_id = patients.id
  JOIN beds ON encounters.bed_id = beds.id
  JOIN locations ON beds.location_id = locations.id
  JOIN (
    select tk.patient_id
           jsonb_agg(to_jsonb(tk)) as all_tasks
    from tasks tk
    group by tk.patient_id
  ) tsk ON patients.id = tsk.patient_id

WHERE encounters.end IS null

to_jsonb(tk) will convert the complete row from the tasks table to a JSON value. If you just want to pick a few columns, then use jsonb_build_object instead as you already do: (jsonb_agg(jsonb_build_object(...))

If you want to exclude only one or two columns, using - might be easier, e.g. to_jsonb(tk) - 'created_at' - 'modified_at' to remove the created_at and modified_at keys from the resulting JSON.

CodePudding user response：

To aggregate Tasks json objects in array for each patient you can use a CTE like this

WITH patient_tasks AS (
    SELECT 
        patients.id, 
        json_agg(
            jsonb_build_object(
                'id', Tasks.id, 
                'title', Tasks.title, 
                'completed', Tasks.completed)
        ) AS tasks
    FROM patients
    JOIN Tasks ON Patients.id=Tasks.patient_id
    GROUP BY patients.id
)
SELECT

Encounters.id as encounter_id,
Encounters.start, Encounters.end, Encounters.estimated_end,

Patients.id as patient_id,
Patients.healthid, Patients.localid, Patients.governmentid, Patients.hexeid,
Patients.given_names, Patients.last_name, Patients.gender, Patients.admin_gender,
Patients.birth_date, Patients.birth_time, Patients.deceased,

Beds.id as bed_id,
Beds.number, Beds.bay, Beds.cubicle, Beds.level, Beds.clean,

Locations.id as location_id,
Locations.title,

patient_tasks.tasks

FROM Encounters
INNER JOIN Patients ON Encounters.patient_id = Patients.id
INNER JOIN Beds ON Encounters.bed_id = Beds.id
INNER JOIN Locations ON Beds.location_id = Locations.id
INNER JOIN patient_tasks ON patient_tasks.id = Encounters.patient_id

WHERE Encounters.end IS null