Multi key sorting and filtering

multi-key-sorting-and-filtering

Summary

Apply multi-field sorting and record-level filtering to extracted JSON tabular data using sort_by, sort_order, and test directives in the matrix directive handler. This skill enables selective organization and subset extraction of records based on custom Boolean conditions and field-based ordering.

When to use

When converting tabular data to JSON via the matrix directive, you need to exclude records that fail domain-specific validation (e.g., only retain records where a 'test' condition evaluates to true) and/or reorder the output list by one or more fields in ascending or descending order. This is typical after field-to-header mapping and collation, when the output record set requires both filtering and ranked presentation.

When NOT to use

• Input records have not yet been mapped to output dictionary keys via fields_to_headers — apply field mapping first.
• The test condition logic is too complex for a simple Boolean expression and requires stateful or cross-record comparisons — use the custom code field instead.
• Records are already in the desired sort order and no filtering is needed — skip this step to avoid redundant processing.

Inputs

• Conversion directives JSON file with matrix-type directive configuration (including sort_by, sort_order, test sub-fields)
• Input JSON data containing records to be filtered and sorted
• Field mapping configuration (fields_to_headers) and collation directives (if applicable)

Outputs

• Filtered and sorted list of dictionaries (records) matching test conditions
• Ordered output JSON representation ready for custom code execution or final serialization

How to apply

Within the matrix-type directive configuration, set the 'sort_by' field to specify the column(s) to order by, 'sort_order' to 'ascending' or 'descending', and 'test' to a Boolean expression that evaluates against record field values to determine inclusion. Parse the directives file to extract these sub-fields, load the input JSON records, apply the test condition to retain only qualifying records, then sort the filtered list using the specified sort_by field(s) and sort_order. The sort and filter operations are applied after field mapping and collation but before custom code execution, ensuring a clean, ordered, and validated record set for downstream transformations or output.

Related tools

• MESSES (Command-line and library framework that implements the matrix directive handler with sort_by, sort_order, test, and fields_to_headers directives for tabular JSON conversion) — https://github.com/MoseleyBioinformaticsLab/messes
• jsonschema (Validates the converted JSON output against the Experiment Description Specification and Protocol Dependent Schema to ensure filtered and sorted records conform to schema constraints) — https://pypi.org/project/jsonschema/
• Python (Implementation language for iterating over records, evaluating test conditions, and applying multi-key sort operations in the matrix directive handler)

Evaluation signals

• Output record count matches expected value after applying test filter (verify no records are dropped or duplicated unintentionally).
• All retained records satisfy the test condition (sample spot-check or full validation against test expression).
• Output list is correctly ordered according to sort_by field(s) and sort_order (ascending or descending), confirmed by inspecting the first and last records or by programmatic comparison.
• All specified headers from fields_to_headers are present in each output dictionary.
• Exclusion_headers fields are absent from output records, confirming field-level filtering was applied correctly.

Limitations

• The test condition must be expressible as a simple Boolean evaluation against individual record fields; complex cross-record or stateful logic requires custom Python code execution instead.
• Sorting performance degrades with very large record counts in memory; no out-of-core or streaming sort options are documented.
• The sort_by and sort_order directives apply to the entire output list after collation, not to individual groups within collated records; use custom code for intra-group sorting.
• Field values used in sort_by must be comparable (strings, numbers, dates); mixed types may produce unpredictable sort order unless converted via the values_to_str directive first.

Evidence

• [other] The matrix directive handler operates by iterating over records in a specified input JSON table to build dictionaries, with capabilities to sort and filter records using sort_by, sort_order, and test fields: "iterating over records in a specified input JSON table to build dictionaries, with capabilities to sort and filter records using sort_by, sort_order, and test fields"
• [other] Parse the conversion directives file to extract the matrix-type directive configuration, including headers, collate, fields_to_headers, exclusion_headers, values_to_str, sort_by, sort_order, test, and code sub-fields.: "Parse the conversion directives file to extract the matrix-type directive configuration, including headers, collate, fields_to_headers, exclusion_headers, values_to_str, sort_by, sort_order, test,"
• [other] Filter records according to the test condition (if provided) and exclusion_headers criteria to retain only qualifying records.: "Filter records according to the test condition (if provided) and exclusion_headers criteria to retain only qualifying records"
• [other] Sort the resulting list of dictionaries according to sort_by field(s) in sort_order (ascending or descending).: "Sort the resulting list of dictionaries according to sort_by field(s) in sort_order (ascending or descending)"
• [intro] To support the JSON-to-JSON conversion a relatively simple set of directives were developed: "To support the JSON-to-JSON conversion a relatively simple set of directives were developed"