Need help to add a simple scripted validator for a multiselect checkbox

I'm using SSIS to query JIRA.

One of the fields being returned is part of a multi-select. The fields/checkboxes designates one part of a whole and the other designates the other part of the whole. 

When I query for these, these fields show up as NULL.

How can you query a multiselect (checkbox) fields?

I would assume as long as I have the field name and the right datatype set (I've tried boolean and string). Yet this returns null regardless.

Is there a trick to querying these types fields?

1 answer

0 votes

Interesting use of a Space Station Information System, but hey, NASA got robots to Mars using the Atlassian stack ;-)

I'm assuming you are using some form of database query.  To keep this simple, this is generally a very Bad Idea (tm).  A JIRA database is absolutely not designed for any form of direct database reporting and you'll find your queries are complex and ugly.  If, for example, you want a query for "everything on an issue", you've got at least 18 tables to read, often with multiple joins and plenty of potential for nulls and esoteric data. 

Anyway.  A multiselect custom field (I'm assuming it's a custom field) will have a record in customfield, tied to an issue.  But only if there is data.  If the field has no checks at all, there will not be any rows in the database with that issue id.  If it has one check, you'll get rows with data.

I guess we'd need to see your exact query to help you if you do think it has data in it!

Hi Nic,

Thank you for the response. Here are some more details regarding the issue.

This is a customfield, and we have many more which are working. What is unique with this field is that it's a multiselect. 

Now I'm querying the JIRA DB from SQL Server using SSIS, there is a 3rd party plugin that can pull the JIRA schema and allow you to add any custom fields as you see fit.

Here is that bit of code to define the custom fields:

<attr name="Requested_Images" xs:type="string" other:outputname="fields_customfield_14352" other:supportedoperators="LIKE,NOT LIKE,IS,IS NOT" other:urlname="customfield_14352" desc="Requested_Images." />

Now my actual query where I know the multi-select has been set is pretty simple:

select * from Issues where [ProjectId] = 10060
 AND key = 'TEMPLATES-22653'

I assume the issue is with my type "string". I've tried integer, boolean. No luck.

Is the logic different for these type of fields?

No, your query is utterly wrong.  This is *exactly* one of the reasons you should not be using SQL to read it - you don't know how to read the database at all.  I'd say whatever your plugin is has the same problem.

Custom field data is held in customfieldvalue.

I think you're misunderstanding.

This query is done in SSIS through a third party tool that utilizes the schema, Essentially I can alter the schema via the 3rd party tool to account for 'references' to the customfield table.

However for certain customfield 'types' the returned value is either not what i expected or not stored the same.


If I run the query I described above for another customfield that is just a text field. Everything works just fine. I understand not querying from SQL due to the associations or complexitiy of the schema, but that is solved by the plugin (Or so it is supposed to be). Just wanting to understand the types of fields and how that translates into SQL.

I'll contact the plugin creator and see what's up. 

Just curious if there is something unique about multi-selects or dropdowns and how they are stored in JIRA? I'm not the admin so I can't gain access to the JIRA DB directly.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,025 views 4 9
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you