Search Query Config Cleanup Guide
Overview
This guide provides instructions for cleaning up deleted characteristic IDs from the search_query_config table. This cleanup is necessary when characteristic IDs are removed from the com.statista.numera.search.characteristic.Characteristics enum but still referenced in the database.
When to Use This Guide
Use this cleanup process when: - Characteristic IDs have been removed from the Characteristics enum - You need to clean up orphaned references in the database - You're preparing for a schema migration or data cleanup
Prerequisites
- Database access to the target environment (dev, staging, or production)
- Administrative privileges to execute UPDATE and DELETE statements
- A backup of the
search_query_configtable (recommended before running cleanup)
Database Connection
Local Database
If running against a local docker container:
docker exec -it <CONTAINER_ID> psql -U numera -d contentws
Remote Database (Dev/Staging/Production)
Follow the jump-host configuration guide to connect to remote databases.
Step-by-Step Cleanup Process
Step 1: Create Backup (Recommended)
Before running the cleanup, create a backup of the affected table:
-- Create a backup table
CREATE TABLE search_query_config_backup AS
SELECT * FROM search_query_config;
-- Verify backup
SELECT COUNT(*) FROM search_query_config_backup;
Step 2: Create Valid Characteristic IDs Table
Create a temporary table containing all valid characteristic IDs from the Characteristics enum:
CREATE TEMPORARY TABLE valid_characteristic_ids (id int);
INSERT INTO valid_characteristic_ids (id) VALUES
(1), (2), (3), (9), (10), (11), (13), (14), (15), (16), (17), (18), (19), (20),
(21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34),
(36), (37), (38), (39), (41), (42), (43), (44), (45), (46), (47), (49), (80), (81),
(50), (51), (52), (54), (82), (83), (85), (86), (87), (88), (89), (90), (91), (92),
(93), (94), (95), (96), (55), (56), (57), (59), (100), (101), (65), (66), (68), (69),
(74), (75), (76), (77), (78), (102), (103), (104), (105), (106), (107), (108), (109);
Note: Update this list to match the current valid characteristic IDs from your
Characteristicsenum.
Step 3: Preview Affected Records
Before making changes, preview which records will be affected:
-- Check filters that will be cleaned up
SELECT id, filters
FROM search_query_config
WHERE filters IS NOT NULL
AND filters::jsonb != '[]'::jsonb
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(filters::jsonb) AS filter_obj
WHERE (filter_obj->>'cid')::int NOT IN (SELECT id FROM valid_characteristic_ids)
);
-- Check or_filter that will be cleaned up
SELECT id, or_filter
FROM search_query_config
WHERE or_filter IS NOT NULL
AND or_filter::jsonb ? 'characteristicIds'
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(or_filter::jsonb->'characteristicIds') AS char_id
WHERE CASE
WHEN jsonb_typeof(char_id) = 'number' THEN char_id::text::int
WHEN jsonb_typeof(char_id) = 'string' THEN (char_id #>> '{}')::int
ELSE NULL
END NOT IN (SELECT id FROM valid_characteristic_ids)
);
Step 4: Clean Up Filters Column
Remove filter objects where the cid doesn't exist in valid characteristic IDs:
UPDATE search_query_config
SET filters = (
SELECT jsonb_agg(filter_obj)
FROM jsonb_array_elements(filters::jsonb) AS filter_obj
WHERE (filter_obj->>'cid')::int IN (SELECT id FROM valid_characteristic_ids)
)
WHERE filters IS NOT NULL
AND filters::jsonb != '[]'::jsonb
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(filters::jsonb) AS filter_obj
WHERE (filter_obj->>'cid')::int NOT IN (SELECT id FROM valid_characteristic_ids)
);
What this does: Removes invalid characteristic ID references from the filters column.
Step 5: Clean Up Settings Column
Clean up the settings column, which has nested cid references in multiple locations:
- settings.settings[].display.sortOptions[].cid
- settings.settings[].display.columns[].cid
- settings.settings[].display.urlColumnIds[]
UPDATE search_query_config
SET settings = (
CASE
WHEN settings::jsonb ? 'settings' AND jsonb_typeof(settings::jsonb->'settings') = 'array' THEN
jsonb_set(
settings::jsonb,
'{settings}',
(
SELECT jsonb_agg(
CASE
WHEN setting_obj ? 'display' AND jsonb_typeof(setting_obj->'display') = 'object' THEN
jsonb_set(
jsonb_set(
jsonb_set(
setting_obj,
'{display,sortOptions}',
CASE
WHEN jsonb_typeof(setting_obj->'display'->'sortOptions') = 'array' THEN
COALESCE(
(
SELECT jsonb_agg(sort_opt)
FROM jsonb_array_elements(setting_obj->'display'->'sortOptions') AS sort_opt
WHERE (sort_opt->>'cid')::int IN (SELECT id FROM valid_characteristic_ids)
),
'[]'::jsonb
)
ELSE
COALESCE(setting_obj->'display'->'sortOptions', '[]'::jsonb)
END,
true
),
'{display,columns}',
CASE
WHEN jsonb_typeof(setting_obj->'display'->'columns') = 'array' THEN
COALESCE(
(
SELECT jsonb_agg(col)
FROM jsonb_array_elements(setting_obj->'display'->'columns') AS col
WHERE (col->>'cid')::int IN (SELECT id FROM valid_characteristic_ids)
),
'[]'::jsonb
)
ELSE
COALESCE(setting_obj->'display'->'columns', '[]'::jsonb)
END,
true
),
'{display,urlColumnIds}',
CASE
WHEN jsonb_typeof(setting_obj->'display'->'urlColumnIds') = 'array' THEN
COALESCE(
(
SELECT jsonb_agg(url_col_id)
FROM jsonb_array_elements(setting_obj->'display'->'urlColumnIds') AS url_col_id
WHERE url_col_id::int IN (SELECT id FROM valid_characteristic_ids)
),
'[]'::jsonb
)
ELSE
COALESCE(setting_obj->'display'->'urlColumnIds', '[]'::jsonb)
END,
true
)
ELSE
setting_obj
END
)
FROM jsonb_array_elements(settings::jsonb->'settings') AS setting_obj
),
true
)
ELSE
settings::jsonb
END
)
WHERE settings IS NOT NULL;
What this does: Removes invalid characteristic IDs from all nested locations within the settings column.
Step 6: Clean Up Or_Filter Column
Remove characteristic IDs from the characteristicIds array in the or_filter column:
UPDATE search_query_config
SET or_filter = jsonb_set(
or_filter::jsonb,
'{characteristicIds}',
COALESCE(
(
SELECT jsonb_agg(char_id)
FROM jsonb_array_elements(or_filter::jsonb->'characteristicIds') AS char_id
WHERE CASE
WHEN jsonb_typeof(char_id) = 'number' THEN char_id::text::int
WHEN jsonb_typeof(char_id) = 'string' THEN (char_id #>> '{}')::int
ELSE NULL
END IN (SELECT id FROM valid_characteristic_ids)
),
'[]'::jsonb
),
true
)
WHERE or_filter IS NOT NULL
AND or_filter::jsonb ? 'characteristicIds'
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(or_filter::jsonb->'characteristicIds') AS char_id
WHERE CASE
WHEN jsonb_typeof(char_id) = 'number' THEN char_id::text::int
WHEN jsonb_typeof(char_id) = 'string' THEN (char_id #>> '{}')::int
ELSE NULL
END NOT IN (SELECT id FROM valid_characteristic_ids)
);
What this does: Removes invalid characteristic IDs from the or_filter.characteristicIds array.
Step 7: Delete Empty Configuration Records
Remove search_query_config records that have no meaningful data after cleanup:
DELETE FROM search_query_config
WHERE (filters IS NULL OR filters::jsonb = '[]'::jsonb)
AND (or_filter IS NULL OR
NOT (or_filter::jsonb ? 'characteristicIds') OR
or_filter::jsonb->'characteristicIds' = '[]'::jsonb)
AND (settings IS NULL
OR settings::jsonb = '{}'::jsonb
OR NOT (settings::jsonb ? 'settings')
OR settings::jsonb->'settings' = '[]'::jsonb
OR NOT EXISTS (
SELECT 1
FROM jsonb_array_elements(settings::jsonb->'settings') AS setting_obj
WHERE setting_obj ? 'display'
AND jsonb_typeof(setting_obj->'display') = 'object'
AND setting_obj->'display' ? 'columns'
AND jsonb_typeof(setting_obj->'display'->'columns') = 'array'
AND setting_obj->'display'->'columns' != '[]'::jsonb
));
What this does: Deletes configuration records that only had invalid characteristic IDs and are now empty.
Step 8: Clean Up Temporary Table
DROP TABLE IF EXISTS valid_characteristic_ids;
Step 9: Verify Results
After running the cleanup, verify the results:
-- Count remaining records
SELECT COUNT(*) FROM search_query_config;
-- Verify no invalid characteristic IDs remain in filters
SELECT COUNT(*)
FROM search_query_config
WHERE filters IS NOT NULL
AND EXISTS (
SELECT 1
FROM jsonb_array_elements(filters::jsonb) AS filter_obj
WHERE (filter_obj->>'cid')::int NOT IN (
1, 2, 3, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20,
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34,
36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 47, 49, 80, 81,
50, 51, 52, 54, 82, 83, 85, 86, 87, 88, 89, 90, 91, 92,
93, 94, 95, 96, 55, 56, 57, 59, 100, 101, 65, 66, 68, 69,
74, 75, 76, 77, 78, 102, 103, 104, 105, 106, 107, 108, 109
)
);
This should return 0 if the cleanup was successful.
Rollback Procedure
If you need to rollback the changes:
-- Drop the current table
DROP TABLE search_query_config;
-- Restore from backup
ALTER TABLE search_query_config_backup RENAME TO search_query_config;
Important Notes
- Always create a backup before running cleanup operations
- Test on a non-production environment first before running on production
- Update the valid characteristic IDs list (Step 2) to match your current
Characteristicsenum - The cleanup process is transactional - wrap it in a transaction if you want to review before committing:
BEGIN;
-- Run all cleanup steps here
-- Review the results
COMMIT; -- or ROLLBACK; to undo
Troubleshooting
Issue: Performance is slow
If the cleanup is taking too long:
- Run the cleanup during off-peak hours
- Consider adding indexes on the affected columns before running cleanup
- Process records in batches using LIMIT and pagination
Issue: Unexpected deletions
If too many records are being deleted in Step 7: - Review the deletion criteria - Check if the valid characteristic IDs list is complete - Consider modifying the deletion logic to be more conservative