Find cases that have an open hospitalization and are not in hospitalization status

[maennchen]

SELECT
    cases.uuid,
    cases.human_readable_id,
    cases.status,
    tracer.display_name AS tracer,
    supervisor.display_name AS supervisor
FROM cases
JOIN users AS tracer ON cases.tracer_uuid = tracer.uuid
JOIN users AS supervisor ON cases.supervisor_uuid = supervisor.uuid
CROSS JOIN UNNEST(cases.hospitalizations) AS hospitalization
WHERE
    cases.status <> 'hospitalization' AND
    hospitalization->>'start' IS NOT NULL AND
    hospitalization->>'end' IS NULL