Insight plugin: retrieve value of an object type's attribute by querying Jira's database

Question

Good evening all,

In my Jira instance I have configured Insight plugin. I have several object types, among those there is one named "Cespiti". Cespiti object has an attribute named "STATO", whose value, in the attached screenshot, is "EROGAZIONE".

Could you suggest how to query the data base to retrieve the value of Stato attribute for a Cespite object?

Thank you for your help.

Regards

Giorgia

Giorgia Fineschi · Answer

Thank you tokcum. I was actually looking for some advice about how to join the database's table with SQL. I need to make some reports (with an external business intelligence tool) which read data from the data base.

I think the solution is:

select distinct * from "AO_8542F1_IFJ_OBJ_ATTR_VAL" attr_val 
LEFT JOIN "AO_8542F1_IFJ_OBJ" obj2 on obj2."ID" = attr_val."REFERENCED_OBJECT_ID"
INNER JOIN "AO_8542F1_IFJ_OBJ_ATTR" attr ON ATTR."ID" = attr_val."OBJECT_ATTRIBUTE_ID" 
INNER JOIN "AO_8542F1_IFJ_OBJ_TYPE_ATTR" type_att ON attr."OBJECT_TYPE_ATTRIBUTE_ID" = type_att."ID" AND type_att."NAME"='STATO'
INNER JOIN "AO_8542F1_IFJ_OBJ" obj on obj."ID" = attr."OBJECT_ID"

Early I was struggling with it because I thought my local copy of the database I was working on was still aligned with what Jira's interface showed. Actually some colleagues had edited my "Cespiti" and the table's data didn't match what the application displayed. It was very confusing...
If someone notices errors in my query, please don't hesitate to let me know.

Thank you.

tokcum · Answer

Hi Georgia,

welcome to the Atlassian community.

Are you looking for Insight objects with this attribute value or for issues connected to such objects. In the first case you need IQL, Insight Query Language within Insight Search, in the second case you need JQL, Jira Query Language with Jira Search.

I assume you are looking for the Insight objects, so you could use:

objectType = CESPITI AND STATO.”STATO CESPITI” = EROGAZIONE

Please be aware that I suggest this without being able to test it. Do not hesitate to contact me again if this doesn’t work.

Kind regards,

Tobias