-- Query to retrieve a bpmn for a given input
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di ON di.name = 'input_name' AND di.version = dt.version
WHERE di.input_xml->>'input_variable' = 'input_value';

-- Query to retrieve a bpmn for a multiple inputs
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di1 ON di1.name = 'input_name_1' AND di1.version = dt.version
JOIN decision_input di2 ON di2.name = 'input_name_2' AND di2.version = dt.version
WHERE di1.input_xml->>'input_variable_1' = 'input_value_1'
  AND di2.input_xml->>'input_variable_2' = 'input_value_2';

-- Query to retrieve specifc version of BPMN process definition for a given input 
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di ON di.name = 'input_name' AND di.version = dt.version
WHERE di.input_xml->>'input_variable' = 'input_value'
  AND pd.version = 2;

-- Query to retrieve specifc version of DMN for a given input
SELECT name, version
FROM decision_table
WHERE name = 'decision_table_name'
  AND version = 2
  AND table_xml->>'input_variable' = 'input_value';