-- search CISA vulnerabilities
SELECT COALESCE(json_group_array(json_object(
         'vuln_id', a.vuln_id,
         'cve_id', c.nvd_id,
         'vendor', d.name,
         'product', e.name,
         'name', b.name,
         'added_at', b.added_at,
         'action', b.action,
         'due_at', b.due_at,
         'rank', a.min_rank
       )), '[]')

  FROM (
    SELECT vuln_id,
           MIN(rank) AS min_rank
      FROM cisa_vulns_fts
     WHERE cisa_vulns_fts MATCH :q
     GROUP BY vuln_id
  ) a
  JOIN cisa_vulns b
    ON (b.vuln_id = a.vuln_id)
  JOIN cisa_vuln_cve_ids c
    ON (c.vuln_id = b.vuln_id)
  JOIN cisa_vendors d
    ON (d.vendor_id = b.vendor_id)
  JOIN cisa_products e
    ON (e.product_id = b.product_id)

 ORDER BY a.min_rank;