Query

SELECT
    vt.asset_name,
    i.id AS indicator_id,
    i.name AS indicator_name,
    jsonb_agg(DISTINCT fm.failure_mode_id) AS orphaned_failure_mode_ids
FROM indicator i
CROSS JOIN LATERAL jsonb_array_elements(i.threshold) AS t(threshold)
CROSS JOIN LATERAL jsonb_array_elements(t.threshold -> 'actions') AS a(action)
LEFT JOIN LATERAL jsonb_array_elements_text(a.action -> 'failureModeIds') AS fm(failure_mode_id) ON true
LEFT JOIN failure_mode f
    ON f.id = fm.failure_mode_id
LEFT JOIN v_twin vt
    ON vt.id = i.twin_id
WHERE 
    a.action ->> 'type' = 'advisory'
    AND COALESCE((a.action ->> 'inheritFailureModes')::boolean, false) = false
    AND fm.failure_mode_id IS NOT NULL
    AND f.id IS NULL
GROUP BY
    i.id,
    i.name,
    vt.asset_name
ORDER BY
    vt.asset_name;