SELECT
sensor_alerts.*,
sensor_instances.*,
json_agg(groups.*) as groups
FROM sensor_alerts
LEFT JOIN sensor_instances ON sensor_instances.id = sensor_alerts.sensorid
LEFT JOIN groups ON sensor_alerts.groupid = groups.parentid
GROUP BY sensor_alerts.id; --- скорее всего группировка не обязательна