Is there a built-in syntax for specifying a selection criteria using AO in JQL?
For example: project = FOO and “AO_A3B47_MYTABLE.column_name” contains “some text”
or do I have to write a module in a custom plugin?
They interact with 2 different data stores: JQL is essentially just a wrapper around Lucene while AO is a wrapper around the database. In order for you to provide your data in JQL, you can go one of two ways:
- Create a custom field with he values in it (https://developer.atlassian.com/jiradev/jira-platform/guides/fields/tutorial-creating-a-custom-field-type )
- Create a JQL function: https://developer.atlassian.com/jiradev/jira-platform/building-jira-add-ons/jira-plugins2-overview/jira-plugin-module-types/jql-function-plugin-module
If your data is around the issue object, then a custom field is probably the better approach, otherwise take a look at the jql function.
I’ve used jQL functions to great success while retrieving records from the AO database.
OK. I have written JQL modules to provide users an abstraction from a complex query. Doing so for AO will not be difficult. I was just curious since AO is relatively ‘baked-in’ and did not find any ‘baked-in’ mechanism to query. Thank you.
Good luck, be sure to ask any questions along the way if you need to! 
Just had a thought, another option would be to index your details against Issue Properties. This would allow another mechanism to add data for querying.
Yes, thanks. I have used that approach as well, and like that it works with JQL. Yet, our data model in some cases has a many-to-many relationship which begs for a separate table. And we have a requirement to be able to query the data via SQL from a datamart.
Yeah, I agree. I’ve only found properties minutely helpful; Typically I am attaching ‘lists’ to issues, and properties don’t support arrays, I haven’t found them to be very helpful.
Is there any drawback in storing a Custom Field’s data in AO? It seems that’s what you are doing, so have you met any issue with that?
If you have a field store the data in the field. In my case a field was not suitable so I created a AO table and a JQL function to return issues appropriately.
I’ve seen some funky generated queries so possibly consider how large data sets will behave.
Several years later, this became an issue with the quantity of records I was clearing and returning. However, I simply added Atlassian cache lib and that solved my issue.