AWS DMS Selection Rules JSON

create_dms_selection_rules.sql

/*
* This query creates JSON input for AWS DMS (Data Migration Service) selection rules.
* It incluedes all schemas. 
*/

WITH rules AS (
  SELECT
      format('{
        "rule-type": "selection",
        "rule-id": "%s",
        "rule-name": "%s",
        "object-locator": {"schema-name": "%s", "table-name": "%%" },
        "rule-action": "include"
      }' , row_number() over (), row_number() over (), n.nspname
      )::jsonb AS rule
    FROM pg_namespace n
    WHERE
      n.nspname !~ '^pg_' AND
      n.nspname <> 'information_schema'
    ORDER BY n.nspname  
)

SELECT
    json_build_object('rules', json_agg(rule))
  FROM rules

Sample output;

{
  "rules": [
    {
      "rule-id": "1",
      "rule-name": "1",
      "rule-type": "selection",
      "rule-action": "include",
      "object-locator": {
        "table-name": "%",
        "schema-name": "public"
      }
    }
  ]
}