How to create index inside Array

Question

Question

Ba Moser · Aug 25, 2020

I use these example classes:
Class Sample.Address Extends (%SerialObject, %Populate)
{Property Street As %String;
Property City As %String;
Property State As %String(POPSPEC = "USState()");
Property Zip As %String;}

Class Sample.Person1 Extends (%Persistent, %Populate)
{ Property Name As %String;
Property Home As Sample.Address;
Property AR As array Of Sample.Address; }

In SQL it is Table Sample.Person1

No problem to define INDEX on Home_State

But Table Sample.Person1_AR

HowTo define an INDEX on AR_State ?
Index st On AR.State;

Does not compile!

Discussion (12)3

Log in or sign up to continue

Benjamin De Boe · Aug 25, 2020

This is not possible today, but happens to be something due for 2020.4 or 2021.1 in the form of expression indices.

The documentation may be slightly confusing here as the BuildValueArray() mechanism applies to non-collection properties and not "array of" or "list of" properties. The section that describes it just happens to be placed right after the section describing how to use the ELEMENTS trick to index them in their entirety. Note that you can use BuildValueArray() for %ArrayOfObjects properties, but those aren't projected as a child table.

A possible workaround would be through a separate property that's just there as an intermediary for your index:

Property AR As array Of Sample.Address;
Property AllStates As %String [ Calculated, ReadOnly, SqlComputeCode = {set {*} = {%%ID}}, SqlComputed ];
Index AllStatesIDX On AllStates(ELEMENTS);
ClassMethod AllStatesBuildValueArray(value, ByRef valueArray) As %Status
{
  kill valueArray
  set tObj = ..%OpenId(value), tKey = ""
  for {
    set tAddress = tObj.AR.GetNext(.tKey)
    quit:tKey=""
    set valueArray(tKey) = tAddress.State
  }
  quit $$$OK
}

Then you can include it in queries:

select * from sample.person where FOR SOME %ELEMENT(AllStates) (%VALUE = 'KY')

4 0

Timothy Leavitt · Aug 25, 2020

I'm intrigued to hear about expression indices - sounds really cool.

Without those, another option is just to have a separate class/table. Suppose the key to the AR array is the address type (Home, Office, etc.); then you could have:

Class Sample.Person1 Extends (%Persistent, %Populate)
{

Property Name As %String;

Relationship Addresses As Sample.PersonAddress [ Cardinality = children, Inverse = Person ];

}

Class Sample.PersonAddress Extends (%Persistent, %Populate)
{

Relationship Person As Sample.Person1 [ Cardinality = parent, Inverse = Addresses ];

Property Type As %String;

Property Address As Sample.Address;

}

Sample.PersonAddress then can have whatever plain old normal indices you want (except bitmap indices - if you want those, make it one-to-many instead of parent/child).

Generally: any time you add an array property - especially an array of objects - it's worth stepping back and thinking about whether it should just be its own full-blown class/table.

4 0

Timothy Leavitt · Aug 25, 2020

I'll add, a query on this might look like:

select distinct Person->ID, Person->Name from Sample.PersonAddress where Address_State = 'RI' and Type = 'Home'

Note the "arrow syntax" for implicit joins.

2 0

Dan Pasco · Aug 25, 2020

Timothy Leavitt's excellent response notwithstanding, this is supported. I do fully embrace the option presented by Timothy Leavitt. The structures I demonstrate here actually produce a model very close to his and the index, since it includes both KEYS and ELEMENTS is projected to the child table projected from the addresses array. Of course, reversing KEYS and ELEMENTS in the index key specification would make the index more useful for searching on city name.

This definition:

Property addresses As array Of Sample.Address;
Index xA On (addresses(KEYS), addresses(ELEMENTS).City)

Not only works but the filing code also recognizes the ability to fold both properties in the index into the same iterator:

    If ('pIndexHandle)||($Ascii($Get(pIndexHandle("Sample.Person")),5)=1) {
        set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",""))
        While bsv26N1 '= "" {
            Set bsv0N8=$zu(28,##class(Sample.Address).%Open($select(^Sample.PersonD(id,"addresses",bsv26N1)="":"",1:$listbuild(^Sample.PersonD(id,"addresses",bsv26N1)_""))).City,7,32768)
            Set ^Sample.PersonI("xA",bsv26N1,bsv0N8,id)=$listget(bsv0N2,1)
            set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",bsv26N1))
        }
    }

And a quick test shows this structure is produced:

panther.local:XDBC:USER>d ##class(Sample.Person).Populate(10) 

panther.local:XDBC:USER>zw ^Sample.PersonI("xA")

^Sample.PersonI("xA","A886"," GANSEVOORT",3)=""

^Sample.PersonI("xA","B350"," MIAMI",6)=""

^Sample.PersonI("xA","B748"," NEWTON",3)=""

^Sample.PersonI("xA","C135"," UKIAH",9)=""

^Sample.PersonI("xA","C261"," ALBANY",1)=""

^Sample.PersonI("xA","C883"," DENVER",2)=""

^Sample.PersonI("xA","D162"," ST LOUIS",4)=""

And this has been in the product since maybe 2010. I couldn't find the original release note for this but I did find a change that fixed a problem when consolidating the iterators and that fix is present in 2010.2.

0 0

Robert Cemper · Aug 29, 2020

This article may help you.

Effective use of Collection Indexing and Querying Collections through SQL

1 0

score 0 · Answer 1 · 2020-08-25T03:42:04-04:00

I believe this section of the docs has the answer to your question: https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_collection

score 0 · Answer 2 · 2020-08-25T04:14:05-04:00

Thanks!
Index arc On AR(ELEMENTS);

works, but it holds the complete serial object. not just the state.
tried:
ClassMethod ARBuildValueArray(value, ByRef valueArray As %Library.String) As %Status { ..... quit $$$OK}

But it refuses compilation:
ERROR #5272: Can't change final 'Method' : 'BuildValueArray'

score 0 · Answer 3 · 2020-08-25T09:58:25-04:00

Thank you @Benjamin De Boe !
As you described it: I followed the doc not being aware that it does not apply to Array!
Using a calculated property as you describe is excellent!

score 0 · Answer 4 · 2020-08-25T10:04:38-04:00

Thank you @Timothy Leavitt !
A child object gives more flexibility and is easier to understand for people doing support in 1 year or 2

score 0 · Answer 5 · 2020-08-25T10:19:24-04:00

I agree entirely a separate table with FK links between the two is the SQL-friendliest way to go about this today.

With expression indices (and later next year LATERAL JOIN support), we will soon have a practical way to leverage these Object-ish data models in all their beauty from SQL too!

score 0 · Answer 6 · 2020-08-25T10:41:30-04:00

Have you try the following:

Index fcIDX1 On (Invoice(ELEMENTS), Invoice(KEYS)) [ Type = index ];

Index fcIDX2 On Invoice(KEYS) [ Type = index ];

Property Invoice As list Of %String(SQLTABLENAME = "INVOICETABLE", STORAGEDEFAULT = "array") [ SqlFieldName = INVOICE ];

score 0 · Answer 7 · 2020-08-25T18:01:02-04:00

That looks good.
But I can not use the index in SQL

. . . WHERE FOR SOME %ELEMENTS(Sample.Person1.AR) (%value......) works as expected.
But
how can I force the new index ?

. . . WHERE FOR SOME %ELEMENTS(Sample.Person1.AR.State) is not accepted
. . . WHERE FOR SOME %ELEMENTS(Sample.Person1.AR_State) is not accepted
.... ??? what else ??