Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JIRA database customfieldvalue query

Hello, i'm trying to extract from the jira db a customfield information for each issue, everything is good when the information is 'stored' in the stringvalue (customfieldvalue table)

SELECT
CONCAT(p.pkey, '-', ji.issuenum) AS Issue, cfo.customvalue as Customer
FROM
jiraissue ji,
project p,
jiradb.customfield cf, jiradb.customfieldvalue cfv , jiradb.customfieldoption cfo
WHERE
p.pname = 'pname' AND cfo.CUSTOMFIELD='field'
AND ji.project = p.id
AND ji.id = cfv.issue
AND cfv.customfield = cf.id
AND cast(cfv.stringvalue as integer) = cfo.id;

i use a query like that and i get what i need.

for another custom field i noticed that the stringvaule and textvalue is null, and the info is in the numbervalue (customfieldvalue table) 

2021-02-11 10_11_03-MySQL Workbench.png

if i use the query above it returns 0 rows, ive tried also another couple of modification and always get no results.  Anyone can help me please? 

1 answer

1 accepted

2 votes
Answer accepted

Yeah, this is one of many reasons you should not be reading the database.  It's absolutely the worst way to get data out of Jira.

You're going to need to construct a lot more SQL - you need to read the custonfield table to establish what type of field it is, and then conditionally read one of the olther columns for the data value.  When the value is an option (which it will be for select type fields  - check boxes, select, multi-select, radio button etc), you'll need yet another join on to customfieldoption to look up the display value

Please, stop reading the database, it's a dreadful way to get data from Jira

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you