Required custom field with NULL data

Gokul Pradheep

We're Planning to migrate JIRA Server to JIRA cloud site. Before that, we've to ensure there is no NULL data pointed to "required" custom fields and "required" system fields.

Is there any way to see if the custom field/system field with "required" field behaviour having NULL data pointed to it?

 

 

 

[I hope there is possibility to query from JIRA's database but not from JIRA server console but am not sure about this.]

Earl McCutcheon

Hello @Gokul Pradheep ,

Ultimately a required field with a NULL value will occur when a field is changed to be required or added to a project after a project already has issues present that do not have a value in the field, so the issues that do not have a value should not impact future and existing open issues, and would only really come into play if an issue is re-opened, and footprint for issues that meet these criteria should be relatively small and effort-wise identifying the issues in mass to correct possible conflict seems like a lot of overhead for minimal return, and even still SQL adds in even more overhead to the process so I believe it would be the best-case scenario of searching via the JQL methods mentioned previously by @Nic Brough -Adaptavist-  and @Ravi Sagar _Sparxsys_ .

However, grabbing a list of custom fields that are flagged as required is fairly easy and I put together the following SQL as a modified version from This KB Article and I believe will do the trick for identifying all the Required Fields in the instance with the details needed to see what scheme they are connected to, from that point you would want to next review the projects connected to the schemes and then get to the list of issues from which point you can include them in your JQL searches to find out what issues are missing the field value.  NOTE the following SQL is formatted for Postgres so modify accordingly for the database type you are using:

SELECT fieldlayoutitem.ID, fieldlayout, fieldidentifier, isrequired, name FROM fieldlayoutitem LEFT JOIN fieldlayout ON fieldlayoutitem.fieldlayout = fieldlayout.id WHERE isrequired = 'true';

I do not have a working query for the entire ask but If you did want to try and map this back to NULL values on an issue, the table you will ultimately need to get to will be the "customfieldvalue" table for the defined custom field.  Some additional details can be seen in Database - Custom fields and Database schema. 

and some additional articles and posts that have some example SQL queries that should  help bridge the mapping can be seen here:

• https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-all-fields-in-a-project-967318426.html
• https://community.atlassian.com/t5/Jira-questions/which-database-table-holds-custom-fields-information/qaq-p/230385
• https://community.atlassian.com/t5/Jira-Software-questions/SQL-query-to-get-all-the-issues-from-a-JIRA-project/qaq-p/1324224

But again as noted by the others getting a SQL query together for the entire data call will be a fairly involved process to achieve the desired result and overall I have a question about what the goal here would be in doing so, and what do you hope to achieve as the final outcome, as the effort involved seems quite intensive for minimal return?

As you noted you are migrating Jira Server to Cloud, the migration should copy things over as they are in the source system to the destination system, so the existence of NULL values on existing issues should not be a blocker in any way as the issues would have the matching data layout and you would have the same ability to use the JQL to search for empty fields post-migration, as well as if an issue is reopened the missing field data would only be required at that point.   Are you by chance encountering some error while migrating that is leading you to look for these data points to update them?

Regards,
Earl