How to retrieve the list of Jira issues based on the number of objects in Asset Objects customfield from the database ?

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Summary

In Jira Cloud, the Assets object custom field can be configured to store a maximum of 20 objects. In Jira Data Center, there are no such limitations. When migrating from Jira Data Center to Cloud, it would be beneficial to identify a list of all Jira Data Center issues where the number of objects in the asset object custom field exceeds 20.

Environment

Jira Service Management Datacenter

Solution

Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below.

You can run the following query to retrieve all issue keys associated with more than 20 objects in a Asset object custom field.

SELECT count(distinct OJI."OBJECT_ID"),OJI."JIRA_ISSUE_ID",OJI."CUSTOM_FIELD_ID", (JP.pkey || '-' || JI.issuenum) AS "ISSUE_KEY"
FROM "AO_8542F1_IFJ_OBJ_JIRAISSUE" OJI 
  LEFT OUTER JOIN jiraissue JI ON JI."id" = OJI."JIRA_ISSUE_ID"
  LEFT OUTER JOIN project JP ON JP.id = JI.project
  group by OJI."JIRA_ISSUE_ID",OJI."CUSTOM_FIELD_ID",JP.pkey,JI.issuenum HAVING count(distinct OJI."OBJECT_ID")>20 

(info) The queries above were written for PostgreSQL and may need syntax changes depending on your database.


Last modified on Jun 24, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.