-- Deploy jmp:customers to pg
BEGIN;
CREATE FOREIGN TABLE raw_customer_data (key text, val text)
SERVER redis_server
OPTIONS (tablekeyprefix 'jmp_customer_');
CREATE VIEW customers AS
SELECT
MAX(SPLIT_PART(key, '-', 2)) AS customer_id,
JSONB_OBJECT_AGG(SUBSTRING(SPLIT_PART(key, '-', 1) FROM 14), val) AS data
FROM raw_customer_data
WHERE key NOT LIKE 'jmp_customer_id-%' GROUP BY SPLIT_PART(key, '-', 2);
COMMIT;