​
Query a Case Management Table

Query a Case Management Table by filling in the following parameters in the step.
ParameterDescription
Table NameType of Table: Alerts, Attachments, Cases, Custom Table, Observables, Tasks
FieldsField Types
Condition (Optional)Condition that compares two Case Management table field values.
Advanced-Limit (Optional)Query Limit

​
Query a Case Management Table using SQL

Query a Case Management Table using SQL by filling in the following parameters in the step.
Please note that you need to use table and column names and not display names.Down below, is the full list of table and column names mapped to their display names.
ParameterDescription
SQL QueryThe SQL Query
Output FormatOutput Format Types: Table, CSV or JSON
To query associated cases linked to fields across different tables, employ the following SQL query:
  • To query associated cases linked to fields across different tables, use the following SQL query:
To fetch related record objects (e.g., full rows like id, name) for each case: 
SELECT
    c.id AS case_id,
    json_agg(
        json_build_object(
            'id', a.id,
            'name', a.name
        )
    ) AS <inserted_table_name>
FROM cases c
JOIN records_relations rr
    ON (
        (rr.record_id_a = c.id AND rr.table_name_a = 'cases' AND rr.table_name_b = '<inserted_table_name>') OR
        (rr.record_id_b = c.id AND rr.table_name_b = 'cases' AND rr.table_name_a = '<inserted_table_name>')
    )
JOIN <inserted_table_name>
    ON (
        (<inserted_table_name>.id = rr.record_id_a AND rr.table_name_a = '<inserted_table_name>') OR
        (<inserted_table_name>.id = rr.record_id_b AND rr.table_name_b = '<inserted_table_name>')
    )
GROUP BY c.id;
To fetch only the related record IDs instead of full objects: 
SELECT
    c.id AS case_id,
    json_agg(
        CASE
            WHEN rr.table_name_a = '<inserted_table_name>' THEN rr.record_id_a
            ELSE rr.record_id_b
        END
    ) AS <inserted_table_name>_ids
FROM cases c
JOIN records_relations rr
    ON (
        (rr.table_name_a = 'cases' AND rr.record_id_a = c.id AND rr.table_name_b = '<inserted_table_name>') OR
        (rr.table_name_b = 'cases' AND rr.record_id_b = c.id AND rr.table_name_a = '<inserted_table_name>')
    )
GROUP BY c.id;
NOTEReplace <inserted_table_name> with the actual table name that is associated with the cases.
For example, to fetch alert records with selected fields attached to each case, you can use the following SQL query: 
SELECT
    c.id AS case_id,
    json_agg(
        json_build_object(
            'id', a.id,
            'name', a.name
        )
    ) AS alerts
FROM cases c
JOIN records_relations rr
    ON (
        (rr.record_id_a = c.id AND rr.table_name_a = 'cases' AND rr.table_name_b = 'alerts') OR
        (rr.record_id_b = c.id AND rr.table_name_b = 'cases' AND rr.table_name_a = 'alerts')
    )
JOIN alerts a
    ON (
        (a.id = rr.record_id_a AND rr.table_name_a = 'alerts') OR
        (a.id = rr.record_id_b AND rr.table_name_b = 'alerts')
    )
GROUP BY c.id;
To fetch only the alert IDs attached to each case, you can use the following SQL query: 
SELECT
    c.id AS case_id,
    json_agg(
        CASE
            WHEN rr.table_name_a = 'alerts' THEN rr.record_id_a
            ELSE rr.record_id_b
        END
    ) AS alert_ids
FROM cases c
JOIN records_relations rr
    ON (
        (rr.table_name_a = 'cases' AND rr.record_id_a = c.id AND rr.table_name_b = 'alerts') OR
        (rr.table_name_b = 'cases' AND rr.record_id_b = c.id AND rr.table_name_a = 'alerts')
    )
GROUP BY c.id;

​
List of Tables

The full list of table and their column names mapped to their display names.