I keep my promise (yes, it wasn't an April Fool's joke ;)

• The first way is associated with a dummy field for the sake of being able to override the final BuildValueArray method and avoid the following error

  ERROR #5272: Can't change final 'Method' : 'BuildValueArray')

  Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ]
  {
  Index xitmp On (ItemsP(KEYS), ItemsP(ELEMENTS));
  Property Company As %String [ Required ];
  Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
  Property Items As list Of rcc.IC.serItem(POPSPEC = ":4") [ Required ];
  Property ItemsP As %String(COLLATION = "EXACT") [ Calculated, Private, ReadOnly, Required, SqlComputeCode = {s {*} = {Items}}, SqlComputed ];
  ClassMethod ItemsPBuildValueArray(
    value,
    ByRef array) As %Status
  {
    s ptr=0
    while $listnext(value,ptr,val){
      s v=$li(val,1),
        array($li(v,1))="Subject",
        array($li(v,2))="Change",
        array($li(v,3))="Color"
    }
    q $$$OK
  }
  }

  select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow') and %value='Color')

  or if need to find values in any fields

  select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

  For the sake of speed, you can store in the index not all the fields of the serial class, i.e.:

  Index xitmp On ItemsP(KEYS);
  ClassMethod ItemsPBuildValueArray(
    value,
    ByRef array) As %Status
  {
    s ptr=0
    while $listnext(value,ptr,val){
      s v=$li(val,1),
        array($li(v,3))="" ; only Color
    }
    q $$$OK
  }

  select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

  You can also add more dummy fields and accordingly indexes to cover all possible queries.

---

• The second way involves changing the storage and creating a virtual table

  Class rcc.IC.ItemList Extends (%Persistent, %Populate) [ Final ]
  {
  Index xitm On Items(ELEMENTS).Color;
  Index xitm1 On (Items(ELEMENTS).Color, Items(KEYS));
  Property Company As %String [ Required ];
  Property Region As list Of %String(COLLATION = "EXACT", POPSPEC = ":4", VALUELIST = ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
  Property Items As list Of rcc.IC.serItem(POPSPEC = ":4", STORAGEDEFAULT = "array") [ Required ];
  }

  select count(distinct ID) from rcc_IC.ItemList where ItemList_Items->Items_Color in ('blue','yellow') -- index "xitm1" is used

  or even faster

  select count(ID) from rcc_IC.ItemList where FOR SOME %ELEMENT(Items) (%Value in ('blue','yellow')) -- index "xitm" is used

  Of course, the data can be accessed from both tables, just do not forget about SetCollectionProjection GetCollectionProjection (for more information, see my article SQL index for array property elements)

Hi Robert.

I also got carried away with this question and found two more ways to use indexes for a list of serial objects, and you can explicitly specify specific fields in the query, rather than $list (%Value,3).

The speed may be not always the best, but I did the best I could. I tested on Caché (perhaps something has been improved in IRIS in this regard?)

If you're interested, I can share it.

zen(id) works with the component object model, and document.getElementbyId(id) works with the document object model.

See Working with Streams

$LISTFIND certainly does its job well, but there is a better solution.

As the data grows, the search speed will drop, since this solution does not use indexes in any way. Therefore, the best solution is to use the predicate FOR SOME %ELEMENT.

For more details with examples, see one of my articles: SQL Performance Resources (item k. Indexing of non-atomic attributes)

The interactive tutorials with exercises built into the documentation helped me a lot in my time:

• Caché Tutorial Topics Map
• Caché Tutorials

Also included was a database "SAMPLES" (code and data) with examples for every taste. I often used it for performance testing.

In addition, in the <cachesys>\dev\ folder there were numerous demos with source code for various programming languages and connection technologies (С++, C#, Java, PHP, Python, ODBC, JDBC, ADO.NET, ActiveX, XEP, etc.):

\dev\tutorials\
\dev\tutorials\csp
\dev\tutorials\java
\dev\tutorials\mv
\dev\tutorials\pojo
\dev\tutorials\security
\dev\tutorials\UnitTestEx
\dev\tutorials\webservices
\dev\tutorials\zen

\dev\Cache\
\dev\Cache\callin
\dev\Cache\callout
\dev\Cache\cctrlcpp
\dev\Cache\cctrlvb
\dev\Cache\ctrldemo
\dev\Cache\HAcluster

\dev\odbc\samples\
\dev\odbc\samples\php
\dev\odbc\samples\select
\dev\odbc\samples\sqlgateway

\dev\python\
\dev\python\samples
\dev\python\samples3

\dev\cdirect
\dev\cpp\samples
\dev\dblib\samples
\dev\dotnet\samples
\dev\java\samples
\dev\mv\samples
\dev\node.js\samples
\dev\perl\samples

N<=9 (59 chr)

N>9 (62 chr)

Who is shorter? ;)

Examining an XML Subtree

  try{

    

$$$ThrowOnError(##class(%XML.XPATH.Document).CreateFromString("<a><b><c>some content</c></b></a>", .doc))
    $$$ThrowOnError(doc.EvaluateExpression("/a", "b", .field))
    #dim obj As %XML.XPATH.DOMResult = field.GetAt(1)
    
    while obj.Read() {
      if obj.HasValue {
        write obj.Path,": ",obj.Value,!
      }
    }
    
  }catch(ex){
    write "Error ", ex.DisplayString(),!
  }

Result:
b\c\: some content

Creating Class Documentation

PS: Once upon a time (when was Caché) there was a wonderful DOCBOOK database that had the methods you needed.

Here's my old (2012 sic!) article on this topic: Extending the built-in documentation^RU->EN

Here you can read a little more:

• The DocBook Application
• DocBook^wiki

There is another way.
If you want any empty strings to always be treated as null/"" instead of "/$c(0), then there is an documented setting (within the scope of namespace), namely:

^%SYS("sql","sys","namespace",<YOUR_NAMESPACE>,"empty string")

Here is a small example:

Class dc.test Extends %Persistent
{

Property i As %Integer;

Property str As %String;

/// do ##class(dc.test).Test()
ClassMethod Test()
{
 do ..%KillExtent()
 
 try{
  
  do $system.SQL.Purge()
  set ^%SYS("sql","sys","namespace",$namespace,"empty string")=$c(0)

  

; '' -> $c(0)
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(1,'')")
  
  ; null -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(2,null)")
  
  do $system.SQL.Purge()
  set ^%SYS("sql","sys","namespace",$namespace,"empty string")=""

  ; '' -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(11,'')")
  
  ; null -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(22,null)")

  

zwrite ^dc.testD
  kill ^%SYS("sql","sys","namespace",$namespace,"empty string")
 }
 catch(ex){
  write ex.DisplayString(),!
 }
}

}

USER>do ##class(dc.test).Test()
^dc.testD=4
^dc.testD(1)=$lb("",1,$c(0))
^dc.testD(2)=$lb("",2,"")
^dc.testD(3)=$lb("",11,"")
^dc.testD(4)=$lb("",22,"")

Another option just for the fun:

Include %SYS.PTools.Stats

Class dc.test [ Abstract ]
{

ClassMethod setValue(args...) As %Status
{
  quit:args<2 $$$ERROR($$$DataMissing)

  

$$$convertArrayToList(args,list)
  quit:$listlength(list)'=args $$$ERROR($$$RequiredArgumentMissing)
  
  set $list(list,*,*)="",
         var=##class(%Utility).FormatString(list),
         $extract(var,1,3)=$name(%sessionData), ##; or $name(%session.Data)
         @var=args(args)
  quit $$$OK
}

/// do ##class(dc.test).Test()
ClassMethod Test()
{
  new %sessionData

  

try{

    

do $system.OBJ.DisplayError(..setValue("key1", "val1")),
          $system.OBJ.DisplayError(..setValue("key1", "key2", "key3", "key4", "val2")),
          $system.OBJ.DisplayError(..setValue("key1", "key2", , "key4", "val3")),
          $system.OBJ.DisplayError(..setValue())
    
    write !
    zwrite %sessionData
  }
  catch(ex){
    write ex.DisplayString(),!
  }
}

}

See also $SYSTEM.SQL.Schema.QueryToTable()