MySQL Query to get all the values of a custom field from Jira DB

[Ravinder Reddy Singireddy]

Hi,

I am looking for a MySQL query which returns all the values of a custom field that were entered in all the issues of a project.

Any inputs are highly appreciated.

Thank you in anticipation

Regards,

Ravinder Reddy.S

[Earl McCutcheon · Accepted solution]

Hi Ravinder,

Before I give an example query I just wanted to makes sure you have all the info you need to fine tune this as needed so for a quick referance point the database schema can be found here:

• https://developer.atlassian.com/server/jira/platform/database-schema/

The table "customfieldvalue" is going to contain the content of the field under the column "textvalue", The mapping on the table is like this:

mysql> DESCRIBE customfieldvalue;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID | decimal(18,0) | NO | PRI | NULL | |
| ISSUE | decimal(18,0) | YES | MUL | NULL | |
| CUSTOMFIELD | decimal(18,0) | YES | | NULL | |
| UPDATED | decimal(18,0) | YES | | NULL | |
| PARENTKEY | varchar(255) | YES | | NULL | |
| STRINGVALUE | varchar(255) | YES | | NULL | |
| NUMBERVALUE | decimal(18,6) | YES | | NULL | |
| TEXTVALUE | longtext | YES | | NULL | |
| DATEVALUE | datetime | YES | | NULL | |
| VALUETYPE | varchar(255) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
10 rows in set (0.00 sec) 

With the Indexes:
"pk_customfieldvalue" PRIMARY KEY, btree (id)
"cfvalue_issue" btree (issue, customfield)

Then the tables you will need to look at are going to be

• customfield

  • for the field ID used in the CUSTOMFIELD column

• customfieldvalue

  • For the contents of the field via TEXTVALUE
• jiraissue
  • for the issue ID's used in the ISSUE column
• project
  • for the project ID's to map the project to the issue in the jiraissue table

So to tie everything back to the textvalue with the following mappings where I put together a rough query that will give you the issue Key and the custom field value name, where in my example the custom field name is "AAA_Example_Field" the project name is "ProjectA" 

mysql> SELECT concat(p.pkey,'-',ji.issuenum) as issue, cfv.textvalue as AAA_Example_Field FROM jiraissue ji, project p, customfield cf, customfieldvalue cfv WHERE p.pname = 'ProjectA' AND ji.project = p.id AND ji.id = cfv.issue AND cfv.customfield = cf.id AND cf.cfname = 'AAA_Example_Field';
+------------+----------------------+
| issue | AAA_Example_Field |
+------------+----------------------+
| PROJECTA-1 | asdfasdfasdfasdfasdf |
+------------+----------------------+
1 row in set (0.00 sec)

Hope this helps.

Also if anyone else runs into this thread and is using PSQL modify the above query to the following EXE to get the same result:

PSQL=> SELECT (p.pkey || '-' || ji.issuenum) as issue, cfv.textvalue as AAA_Example_Field FROM jiraissue ji, project p, customfield cf, customfieldvalue cfv WHERE p.pname = 'ProjectA' AND ji.project = p.id AND ji.id = cfv.issue AND cfv.customfield = cf.id AND cf.cfname = 'AAA_Example_Field';

Regards,
Earl

[Ravinder Reddy Singireddy]

Hi Earl,

The Query worked for me. Thanks a lot for helping me out.

I further need some more help, I want to use this query in a java script so that i can place the java script in the description of a custom field on create issue screen and compare the box value(date time) with the results of the MySQL query with it and deny creating the ticket if the box value that is entered in "From(Date time field)" is already present in the result of the Query.

Once again thanks for the Query and hope you will help me out with the java script also.

Regards,

Ravinder Reddy.S

[Earl McCutcheon]

Hi Ravinder,

Thanks for the confirmation and glad I could help out. 

For your follow up questions, Jira does not expose any SQL to you on the front end of the application, so if you wanted to do a SQL query to populate data to a field you could look into an add-on that add's in some extended functionality like the add-on "Live SQL Custom Fields"
that looks like it could do the trick, some additional add-on apps that could also work  can be found here.

But taking a step back and looking at the requirements of what your ultimate goal here is to have a Conditional Field based on another Field Value.  and this is something that I am pretty sure you would be able to do with the ScriptRunner app using the "Behaviours" functionality or a "Scripted Condition" based on your date field.

Regards,
Earl