Query Structure by direct database access

Question

With an on-premise installation of JIRA 7.6.x and the Structure plug-in 4.5.x is it possible to query the database to output the Issues within a specific Structure?

Reviewing the database tables there is a table AO_8BAD1B_FOREST with a column whose value is a string-representation of a JSON data structure that has two properties: Rows and Depths, each being an array of positive integers.  From what I can tell the Row values correspond to entries in the AO_8BAD1B_ROW table.  Those whose type is 3 represents an Issue in the Structure and the C_ITEM_LONG_ID column is populated with a value.  However, the C_ITEM_LONG_ID doesn't correspond to an Issue ID/number/key?

Is there any guidance on how to write this SQL query?  Basically, given the ID of a Structure I'd like to retrieve the IDs of the Issues within that Structure -- A flat listing (no nesting) would be an acceptable solution.

Christian Rommel_d65u2p · Answer

Hi Anthony,

you have to divide the row-number-values from AO_8BAD1B_FOREST by 2!

With the resulting value you can look into AO_8BAD1B_ROW and will find in column C_ITEM_LONG_ID the JIRAISSUE.ID - Value.

Christian

Anthony Haxby · Answer

Thanks Micky -- What you've offered is good information.  I understand that the database schema is offered "as-is" and is subject to change at any time (furthermore, it is not accessible in cloud instances).  In spite of that we need to develop operational reports against our on-premise database and it would be advantageous to write direct SQL queries against the Structure tables to retrieve the Issue identifiers.  We're using SQL Reporting Services (SSRS) for the development of the operational reports which doesn't provide a method of invoking REST queries to retrieve data -- This is why I'm interested in a SQL-only solution.

miikhy · Answer

Hi Anthony,

Welcome on Community!

I would definitely not recommend using SQL as the AO schema used by Structure can change at any time without notice. Understanding the storage model is also complex due to the inner complexity of structure.

Why wouldn't you use a JQL query to retrieve the elements and get their IDs through the API eventually?

issue in structure("Structure Name")

should do the trick for the JQL part, save it as a filter and query it through the rest api :)  Should be far easier IMO.

Hope this helps!

Cheers