I find SQL obnoxious, due to its brevity. Getting complicated desired behavior from it sometimes requires clever understanding and combinations of very simple primitives. Because it’s taken me about 2 weeks (off and on) to work it out, I present, with no explanation, this code:
WITH vars (var) AS ( SELECT UNNEST(string_to_array(ancestry, '/')::integer[]) FROM calibrations AS a ) SELECT c.id AS cal_id, p.id AS param_id, t.id AS tuning_id, o.* FROM tunings t LEFT JOIN LATERAL json_array_elements(t.data::json) WITH ORDINALITY AS o ON TRUE JOIN variables v ON v.tuning_id = t.id JOIN calibrations c ON v.calibration_id = c.id JOIN parameters p ON p.label = t.label WHERE c.id NOT IN (SELECT var FROM vars) AND t.data_type = 'Z_Axis' AND p.label = 'C_PME_GainFactor_Table'