DB query: customfeld type and relation to project

Question

Hi!

Making some queries from JIRA DB failed to find relations, please assist me.

1)How customfields are related to project table? Ideally I need to query the list of fields created or related to separate project.

2)How to distinguish different types of custom field?

I have some custom fields in table “customfield” there are two key fields “customfiedltypekey”, “customfieldsearchkey” there must be a way to distinguish the type of the field, LOV for instance.

Thank you!

Daniel Ebers · Answer

Hi Oleksandr,

coming across exactly the same challenge on several Jira server instances from my experience digging into database and/or checking via API is time consuming task.

While we are not affiliated in any way to this App's vendor it helped us a lot:
https://marketplace.atlassian.com/apps/1219634/power-admin

Please feel free to compare with manual API approach. Maybe there are other Apps out there accomplishing the same - we did not check, to be honest.

For an 'automated' process as of DWH export approaches you would have to check if it meets your needs. My use case is always a clean up and pre-migration planning with Power Admin.

Cheers,
Daniel

Oleksandr Muliar_ur6al8 · Answer

I have finished doing my queries - it works eventually.

Now i would like to compare it with API performance.

As of start point - there is a mess in my head with that API.

I have specific project in jira ( i know its pkey from project table).

is there specific API service which will query all issues of the project with its all fields including custom fields?

Thank you very much!

Nic Brough -Adaptavist- · Answer

You should use the REST API to get the data from Jira.

For comparison and ease of use, let me just say that to fetch the data for a single simple issue with SQL needs a query with at least 18 joins, some 2-3 layers deep.  You have to understand exactly how the data hangs together and issue different SQL for different types of data.  Even if you understand the data, you can very easily build SQL that will cripple your database performance.

Or you can issue a single REST call that can't do any harm and gives you all the data you need.

Start with https://developer.atlassian.com/server/jira/platform/rest-apis/