AQL "cross asset" queries

Daniel
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 27, 2024

Hi,

I'm tearing my hair out trying to get some form of AQL to work somehow - and test it.

I have 2 assets in my schema: "Billing Accounts" and "Users".

"Billing Accounts":

  • Name (default label)

"Users":

  • Name
  • Email (default label)
  • Billing Account: Object linking to "Billing Accounts"

This way I have a link between a given user and their corresponding billing account name.

I want to run an AQL query where I can select the a "Billing Accounts" object where that object is linked to a Users object where the Email is equal to a known value.

From the Asset lists, I can write an AQL "advanced query" it fails if I try to reference another object in my schema than the one where I'm trying to run it on.

 

For example, in the "Billing Accounts" asset the AQL query:

"Billing Accounts"."Name" = "My Company"

Returns the My Company asset object as expected.

But

"Billing Accounts"."Name" = "Portal Users"."Billing Account" AND "Portal Users"."Email" = "me@yopmail.com"

Returns an error, despite there being an object with the email me@yopmail.com, and that object is linked against an asset.

I can and often have written SQL joins all day, and even JQL for advanced reporting, but I cannot find a way after 2 days of throwing everything against the wall of getting an AQL query to link 2 objects together...

 

I've tried the same queries, setting up the Asset Object as a custom field, : The field accepts both :

objectType="Billing Accounts" AND objectType = "Users"

and even

schemaType="Customer Support Related Data"

As a valid Filter Scope, but whatever I try in the Filter Issue Scope is always rejected.

I've tried playing with the inboundReferences, outboundReferences, there has to be a way to query and join 2 assets together, otherwise why bother having references, but I just cannot find any way to get a list of objects that are linked to another object where that antother object has a field with a value of xyz...

 

Help...? Please? I can't see where I'm going wrong, and can't find any similar use case discussed or explained on line...

 

 

1 answer

0 votes
Victor Feria September 23, 2024

Hi Daniel,

I have now the same issue as you with join in AQL, did you find a solution?
I believe that AQL does not support it, you can only filter attributes with related objects.

 

Thank you

kate_brenden December 13, 2024

@Daniel you might be able to accomplish this using the 'object HAVING outboundReference() functionality. I'm not familiar enough with it to recommend a solution in your context, but I was able to accomplish something similar in a JIra form where the custom fields listed are selections a user made earlier in the form:

object HAVING outboundReferences("Application Name" = ${customfield_12639.label}) AND object HAVING outboundReferences(object having inboundReferences("Short Name" = ${customfield_12640.label}, referenceType IN (Value)))

In this circumstance, I have 3 object types, A, B, and C. A has an attribute link to B, and C has a attribute link to B. This field will return all objects in C that are matched to A via B. For example,

A = Jira; B = Access to a Role; C = Request a new Jira Role. If I pick Jira, this field will return Request a new Jira role even though there's no direct link between the two objects. 

Anyway, I know that is a *completely* different scenario from what you have outlined, but hopefully you can scrap together pieces of it to get what you are looking for. I've found outbound/inbound reference to be a VERY helpful AQL query because it gives you access to everything in the 'linked objects' box. Good luck!

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events