-- Migration Doctor snapshot, SQL export: all saved filters, including other users' private filters, with JQL and owner username.
-- Read-only: a single SELECT, no writes, no temporary tables. Output: one JSON object per line.
-- Run (read-only) with the mysql client and save the output as filters.jsonl:
--   mysql --batch --raw --skip-column-names --default-character-set=utf8mb4 -h DBHOST -u READONLY_USER -p JIRADB < filters.sql > export/filters.jsonl
-- Needs MySQL 5.7.8 or newer (JSON_OBJECT).
-- Then pass the export directory to the snapshot script: --sql-export-dir export (bash) or -SqlExportDir export (PowerShell).

SELECT JSON_OBJECT(
         'id', s.id,
         'name', s.filtername,
         'description', s.description,
         'jql', s.reqcontent,
         'owner', COALESCE(o.user_name, s.username))
FROM searchrequest s
LEFT JOIN (
  SELECT a.user_key,
         COALESCE((SELECT u.user_name FROM cwd_user u JOIN cwd_directory d ON d.id = u.directory_id AND d.active = 1
           WHERE u.lower_user_name = a.lower_user_name ORDER BY d.directory_position LIMIT 1),
                  a.lower_user_name) AS user_name
  FROM app_user a
) o ON o.user_key = s.username
ORDER BY s.id;