Introduction

When migrating Jira to the cloud, one common problem that arises is the breaking of filters pointing to project IDs or custom field IDs. Filters that rely on specific IDs, such as project = 10212 or cf[12344] = "ABC", will not work post-migration because these IDs often change in the new environment. To address this issue, you can use database queries to update these filters, ensuring they reference project names and custom field names instead of IDs.

Understanding the Problem

Filters in Jira are used extensively to organize and retrieve issues. However, these filters often use hardcoded project and custom field IDs. For example:

  • project = 10212 ORDER BY Rank ASC

Similarly, custom fields can be a problem:

  • cf[10110] = 4 AND project = "Sample Data" ORDER BY cf[10106]

Solution Overview

I used SQL scripts to update the reqcontent field in the searchrequest table. This field contains the filter definitions. The scripts will replace project and custom field IDs with their corresponding names.

Example Transformation

Assume the customfield table contains the following mappings for the relevant IDs:

idcfname
10239“Priority”
10238“Status”
10115“Start Date”
10116“Due Date”

Initial reqcontent

project = SCR AND issuetype in (standardIssueTypes(), Bug, Data, Enhancements, Project, Setup) AND ERD >= 2019-5-1 AND ERD < 2019-12-28 ORDER BY cf[10239] ASC, cf[10238] ASC, cf[10115] ASC, cf[10116] ASC, key ASC

Updated reqcontent

After processing, the reqcontent would be transformed as follows:

project = SCR AND issuetype in (standardIssueTypes(), Bug, Data, Enhancements, Project, Setup) AND ERD >= 2019-5-1 AND ERD < 2019-12-28 ORDER BY "Priority" ASC, "Status" ASC, "Start Date" ASC, "Due Date" ASC, key ASC

Explanation of Changes

  • cf[10239] is replaced with "Priority".
  • cf[10238] is replaced with "Status".
  • cf[10115] is replaced with "Start Date".
  • cf[10116] is replaced with "Due Date".

Queries Explained

Updating Project Names

PostgreSQL

The following PostgreSQL query updates the reqcontent field by replacing project IDs with project names:

WITH project_names AS (
    SELECT id, pname
    FROM project
)
UPDATE searchrequest
SET reqcontent = REGEXP_REPLACE(
    reqcontent, 
    'project = ' || project_names.id, 
    'project = "' || project_names.pname || '"', 
    'g'
)
FROM project_names
WHERE reqcontent LIKE '%project = ' || project_names.id || '%';

This query does the following:

  1. Creates a common table expression (CTE) project_names to store project IDs and names.
  2. Updates the reqcontent field by replacing instances of project = [id] with project = "[name]".

MySQL

For MySQL, we use a stored procedure to perform the update:

DELIMITER //

CREATE PROCEDURE update_project_names()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE project_id INT;
    DECLARE project_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
    SELECT id, pname FROM project;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO project_id, project_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        UPDATE searchrequest
        SET reqcontent = REPLACE(reqcontent, CONCAT('project = ', project_id), CONCAT('project = "', project_name, '"'))
        WHERE reqcontent LIKE CONCAT('%project = ', project_id, '%');
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;
CALL update_project_names();

SQL Server

In SQL Server, we use a cursor to iterate through the projects and update the reqcontent field:

DECLARE @project_id INT;
DECLARE @project_name NVARCHAR(255);

DECLARE cur CURSOR FOR 
SELECT id, pname FROM project;

OPEN cur;
FETCH NEXT FROM cur INTO @project_id, @project_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE searchrequest
    SET reqcontent = REPLACE(reqcontent, 'project = ' + CAST(@project_id AS NVARCHAR(10)), 'project = "' + @project_name + '"')
    WHERE reqcontent LIKE '%project = ' + CAST(@project_id AS NVARCHAR(10)) + '%';
    FETCH NEXT FROM cur INTO @project_id, @project_name;
END

CLOSE cur;
DEALLOCATE cur;

Updating Custom Fields

PostgreSQL

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (
        SELECT id, cfname, 'cf[' || id || ']' AS cf_id_pattern
        FROM customfield
    ) LOOP
        UPDATE searchrequest
        SET reqcontent = regexp_replace(reqcontent, rec.cf_id_pattern, '"' || rec.cfname || '"', 'g')
        WHERE reqcontent ~ rec.cf_id_pattern;
    END LOOP;
END $$;

MySQL

DELIMITER //

CREATE PROCEDURE update_custom_field_references()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cf_id_pattern VARCHAR(50);
    DECLARE cfname VARCHAR(255);
    DECLARE cur CURSOR FOR 
    SELECT CONCAT('cf[', id, ']') AS cf_id_pattern, cfname FROM customfield;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO cf_id_pattern, cfname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        UPDATE searchrequest
        SET reqcontent = REPLACE(reqcontent, cf_id_pattern, CONCAT('"', cfname, '"'))
        WHERE reqcontent LIKE CONCAT('%', cf_id_pattern, '%');
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;
CALL update_custom_field_references();

SQL Server

DECLARE @cfid NVARCHAR(50), @cfname NVARCHAR(255);
 
DECLARE cur CURSOR FOR 
	SELECT id AS cfid, cfname
	FROM customfield;
OPEN cur;
FETCH NEXT FROM cur INTO @cfid, @cfname;
WHILE @@FETCH_STATUS = 0
BEGIN
	UPDATE searchrequest
	SET reqcontent = REPLACE(CONVERT(VARCHAR(MAX), reqcontent), 'cf[' + @cfid + ']', '"' + @cfname + '"')
	WHERE reqcontent LIKE '%cf[[]' + @cfid + '%';
FETCH NEXT FROM cur INTO @cfid, @cfname;
END
CLOSE cur;
DEALLOCATE cur

Conclusion

By running these SQL scripts, you can update your Jira filters to ensure they continue working after migration to the cloud. These scripts replace hardcoded project and custom field IDs with their respective names, making your filters more robust and less likely to break due to ID changes. This approach ensures a smoother transition and reduces the manual effort required to fix filters post-migration.