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 · Accepted solution]

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

[Ravi Sagar _Sparxsys_]

Hi @Earl McCutcheon 

It is so satisfying when someone explains it nicely :) Thanks for sharing the queries and the sql, great explanation.

Ravi

[Gokul Pradheep]

Hi @Earl McCutcheon - Thanks for expaining this practically in every possible angle.

As of now we didn't faced any issue during migration. My organisation JIRA is highly customised and having more number of CF. I've read in documentation that migration may get fail, if "required" CF found to be empty. So we've planned to do clean up activity phase in on-prem JIRA that includes CF removal (if it's configured for 0 screen with NULL historical data). In addition to this, I've requested in this post. However we did manually for each CF and sorted this out as mentioned by Ravi and Nic.

PS: And yes we've faced process overhead issue while we tried to execute modified database query for this and we aborted that.

[Ravi Sagar _Sparxsys_]

Hi @Gokul Pradheep 

Do you want to do it for all the fields or a specific field. You can always use a jql "customfield is EMPTY" for a specific project or issue type where you know this field is required.

Ravi

[Gokul Pradheep]

Hi @Ravi Sagar _Sparxsys_ 

Yes using JQL, we can find for specific custom field. But I need to do it for all the custom field, as we're having many number of custom filed  created. Is there any possibility for this?

[Nic Brough -Adaptavist-]

As Ravi says, "customfield is empty" will find issues where the custom field is not filled.  You'll need to repeat it for each field you want to check.