Discovery: query historical findings from the Phinder findings store,

Source: 19-select-findings-groupby.phisql

PhiSQL

-- Discovery: query historical findings from the Phinder findings store,
-- counting occurrences of each entity type per path. Demonstrates SELECT
-- projection over findings, the COUNT(*) aggregate, a string-comparison
-- WHERE filter, and GROUP BY.
--
-- The findings table schema is normative; columns referenced here must
-- match spec/v1.0/catalog/findings.yaml. NOW() and INTERVAL arithmetic are
-- intentionally not part of v1.0; the example uses an absolute date so the
-- compiled JSON is deterministic.
--
-- Compiles to the JSON in 19-select-findings-groupby.json.

SELECT path, entity_type, COUNT(*)
  FROM phinder.findings
  WHERE scan_date > '2026-04-01'
  GROUP BY path, entity_type;

Compiles to

{
  "operation": "SELECT_FINDINGS",
  "findings_ref": {
    "namespace": "phinder",
    "table": "findings"
  },
  "projection": [
    {"type": "column", "name": "path"},
    {"type": "column", "name": "entity_type"},
    {"type": "aggregate", "function": "COUNT", "argument": "*"}
  ],
  "where": {
    "type": "compare",
    "column": "scan_date",
    "op": ">",
    "value": "2026-04-01"
  },
  "group_by": ["path", "entity_type"]
}