Making the most of $Query
I ran into an interesting ObjectScript use case today with a general solution that I wanted to share.
Use case:
I have a JSON array (specifically, in my case, an array of issues from Jira) that I want to aggregate over a few fields - say, category, priority, and issue type. I then want to flatten the aggregates into a simple list with the total for each of the groups. Of course, for the aggregation, it makes sense to use a local array in the form:
agg(category, priority, type) = total
Such that for each record in the input array I can just:
Do $increment(agg(category, priority, type))
But once I've done the aggregation, I want to get that into an easier form to iterate over, like an integer-subscripted array:
summary = n summary(1) = $listbuild(total1, category1, priority1, type1) ... summary(n) = $listbuild(totalN, categoryN, priorityN, typeN)
Basic Solution:
The simple approach is just to have three nested For loops with $Order - for example:
Set category = ""
For {
Set category = $Order(agg(category))
Quit:category=""
Set priority = ""
For {
Set priority = $Order(agg(category,priority))
Quit:priority=""
Set type = ""
For {
Set type = $Order(agg(category,priority,type),1,total)
Quit:type=""
Set summary($i(summary)) = $listbuild(total,category,priority,type)
}
}
}
That's what I started out with, but it's a lot of code, and if I had more dimensions to aggregate over it'd get unwieldy quickly. It made me wonder - is there a general solution to accomplish the same thing? It turns out, there is!
Better Solution with $Query:
I decided that using $query would help. Note that this solution assumes uniform depth of subscripts/values across the whole local array; weird things would happen if this assumption was violated.
ClassMethod Flatten(ByRef deep, Output flat) [ PublicList = deep ]
{
Set reference = "deep"
For {
Set reference = $query(@reference)
Quit:reference=""
Set value = $listbuild(@reference)
For i=1:1:$qlength(reference) {
Set value = value_$listbuild($qsubscript(reference,i))
}
Set flat($i(flat)) = value
}
}
So the above snippet is replaced with:
Do ..Flatten(.agg,.summary)
A few things to note about this solution:
- deep needs to be in the PublicList for $query to be able to operate on it
- in each iteration, reference is changed to reference the next set of subscripts in deep that has a value - e.g., the value might be: deep("foo","bar")
- $qlength returns the number of subscripts in reference
- $qsubscript returns the value of the i'th subscript of reference
- When $listbuild lists are concatenated, a valid $listbuild list with the combined lists is the result (this is way better than using any other delimiter!)
Summary
$query, $qlength, and $qsubscript are handy for dealing with globals/local arrays of arbitrary depth.
Further Reading
$Query: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI...
$QSubscript: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.c...
$QLength: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.c...
Great article!

$Query is not so popular as his "cousin" $Order.
Nice to see both implementations showing the difference in the construction with the same results.
Just a small fix that makes the PublicList unnecessary:
The fixed method should be called in a slightly different way:
@Alexey Maslov [ ProcedureBlock = 0 ]
This doesn't compile if the calling method doesn't have
calling from terminal has no problem as there are all variables in Global Scope
[ I fell also in this trap
Robert,
you are absolutely correct in your guess: I've just called it from terminal :)
Another option is to put agg into the PublicList of the caller method. I still like it more than passing by reference as it looks clearer.
P.S. Correct caller was added to my sample code. THX Robert.
Nice article Tim! For posterity, I will try to clarify two statements you made:
Some readers might see a conflict in these two statements (I added the bold), but there is no conflict. Statement #2 is correct. Statement #1 applies to using $query for the specific goal Tim had in mind.
It's Friday afternoon, so I thought I'd play with a variation that avoids $Query. This is for no particular reason, as I think the $Q solution is fine, but I wondered if I could:
ClassMethod Flatten(ByRef array, ref = "array") As %List [ PublicList = array ] { S sub="",list="" F { S sub=$O(@ref@(sub),1,data) I sub="" Q I $G(data)'="" { S line="" F i=1:1:$QLength(ref) S line=line_$LB($QSubscript(ref,i)) S list=list_$LB($LB(data)_line_$LB(sub)) } I $D(@ref@(sub))\10 { ; if there are deeper nodes, go down S nextRef=$S(ref["(":$E(ref,1,*-1)_",""",1:ref_"(""")_sub_""")" S list=list_..Flatten(.array,nextRef) } } Return list }
It's recursive, which is dangerous but powerful stuff! :-) Call it by something like: S out=..Flatten(.in)
It also avoids output variables in the parameter list, which I find confusing and against Uncle Bob's clean coding principals (okay, he'd also be horrified by "S" for "set", but I'm old-school). Instead, it returns a list of lists which is just as easy to step through. / Mike
Hi Mike
Nice example of recursion. However, you might want to try testing your solution with some non-sanitized input. For example:
💡 This article is considered as InterSystems Data Platform Best Practice.
Thanks @Timothy Leavitt, these explanations are very valuable for those who are new to intersystems (as me).
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue