Skip to main content

Querying Collections

Querying Collections

Collections may be referenced from the SQL WHERE clause, as follows:

 WHERE FOR SOME %ELEMENT(collectionRef) [AS label] (predicate)

The FOR SOME %ELEMENT clause can be used for list collections and arrays that specify STORAGEDEFAULT="list". The predicate may contain one reference to the pseudo-columns %KEY, %VALUE, or both. A few examples should help to clarify how the FOR SOME %ELEMENT clause may be used. The following returns the name and the list of FavoriteColors for each person whose FavoriteColors include 'Red'.

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

Any SQL predicate may appear after the %Value (or %Key), so for example the following is also legal syntax:

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(Sample.Person.FavoriteColors)
        (%Value IN ('Red', 'Blue', 'Green'))

A list collection is considered a special case of an array collection that has sequential numeric keys 1, 2, and so on. Array collections may have arbitrary non-null keys:

 FOR SOME (children) (%Key = 'betty' AND %Value > 5)

In addition to the built-in list and array collection types, generalized collections may be created by providing a BuildValueArray() class method for any property. The BuildValueArray() class method transforms the value of a property into a local array, where each subscript of the array is a %KEY and the value is the corresponding %VALUE.

In addition to simple selections on the %KEY or %VALUE, it is also possible to logically connect two collections, as in the following example:

   FOR SOME %ELEMENT(flavors) AS f
      (f.%VALUE IN ('Chocolate', 'Vanilla') AND
       FOR SOME %ELEMENT(toppings) AS t
           (t.%VALUE = 'Butterscotch' AND
            f.%KEY = t.%KEY))

This example has two collections: flavors and toppings, that are positionally related through their key. The query qualifies a row that has chocolate or vanilla specified as an element of flavors, and that also has butterscotch listed as the corresponding topping, where the correspondence is established through the %KEY.

You can change this default system-wide using the CollectionProjection option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. SET status=$SYSTEM.SQL.Util.SetOption("CollectionProjection",1,.oldval) to project a collection as a column if the collection is projected as a child table; the default is 0. Changes made to this system-wide setting takes effect for each class when that class is compiled or recompiled. You can use $SYSTEM.SQL.Util.GetOption("CollectionProjection")Opens in a new tab to return the current setting.

For information on indexing a collection, refer to Indexing Collections.

Usage Notes and Restrictions

  • FOR SOME %ELEMENT may only appear in the WHERE clause.

  • %KEY and/or %VALUE may only appear in a FOR predicate.

  • Any particular %KEY or %VALUE may be referenced only once.

  • %KEY and %VALUE may not appear in an outer join.

  • %KEY and %VALUE may not appear in a value expression (only in a predicate).

FeedbackOpens in a new tab