select Statement

select each from anArray where <where-expression> group by <group-by-expression>
select first from anArray where <where-expression> group by <group-by-expression>
select last from anArray where <where-expression> group by <group-by-expression>
select distinct <distinct-expression> from anArray where <where-expression>

Selects array items by evaluating a boolean where-expression for each array element and optionally grouping it. In all expressions the array element is identified by the keyword element.

• The optional keyword each indicates that the select statement returns an Array containing the filtered and grouped elements.

• If only the first or last array element are to be selected, use first respectively last.

• If an array of unique values is to be returned use the keyword distinct.

Please note that select only selects the element references (not the content). The elements of the target array will reference to the same elements as the selected elements from the source array. Changes to an element of the target array will also change the related element in the source array.

Refer to Object References > Arrays for more general details on references and their impact, and an example.

To create an independent target array, use the copy() Operation.

anArray

Can be any variable or object attribute having the type Array.

<where-expression>

This is can be any expression evaluating to Boolean and containing the keyword element. The where-clause (where <where-expression>) is mandatory for all select statements except for select distinct.

<group-by-expression>

This is can be any expression containing the keyword element and returning a Base Type (except Blob).

The resulting (grouped) array is an array of type Group, where values contains the array elements that belong into that group.

The group-by clause (group by <group-by-expression>) is optional. The group-by clause must not be used in conjunction with the distinct clause.

<distinct-expression>

This is can be any expression containing the keyword element returning a Base Type (except Blob).

The distinct-clause (distinct <distinct-expression>) must not be used in conjunction with the group-by clause.

The examples below use an array of Company objects as input:

Select against a specific property from a complex array

Input: an array of articles

{ "articles": [
    {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52},
    {"name": "RC-0002", "category": "Adapter", "serviceInterval": 104},
    {"name": "CD-2002", "category": "Connector", "serviceInterval": 52},
    {"name": "CD-2024", "category": "Connector", "serviceInterval": 208}
]}

• Select all Adapters

  CODE

  set adapters = select each from articles where element.category = "Adapter";

  Result:

  JSON

  { "adapters": [
      {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52},
      {"name": "RC-0002", "category": "Adapter", "serviceInterval": 104}
  ]}

• Select the first Adapter

  CODE

  set adapters = select first from articles where element.category = "Adapter";

  Result:

  JSON

  { "adapters": [
      {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52}
  ]}

Select distinct values

Input: an array of articles

{ "articles": [
    {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52},
    {"name": "RC-0002", "category": "Adapter", "serviceInterval": 104},
    {"name": "CD-2002", "category": "Connector", "serviceInterval": 52},
    {"name": "CD-2024", "category": "Connector", "serviceInterval": 208}
]}

• Select all distinct categories
  (where categories is an array of String)

  CODE

  set categories = select distinct element.category from articles;

  Result:

  JSON

  { "categories": ["Adapter", "Connector"]}

• Select all distinct service intervals lower than 200

  CODE

  set serviceIntervals = select distinct element.serviceInterval from articles
                                         where element.serviceInterval < 200;

  Result:

  JSON

  { "serviceIntervals": [52, 104]}

Grouping Example

Input: an array of articles

{ "articles": [
    {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52},
    {"name": "RC-0002", "category": "Adapter", "serviceInterval": 104},
    {"name": "CD-2002", "category": "Connector", "serviceInterval": 52},
    {"name": "CD-2024", "category": "Connector", "serviceInterval": 208}
]}

Select all articles grouped by category:

set groupedArticles = select each from articles group by element.category:

Result:

{ "groupedArticles": [
  {
    {"key": Adapter"},
    {"values": [
      {"name": "AF-1200", "category": "Adapter", "serviceInterval": 52},
      {"name": "RC-0002", "category": "Adapter", "serviceInterval": 104}
    ]}
  },
  {
    {"key": Connector"},
    {"values": [
      {"name": "CD-2002", "category": "Connector", "serviceInterval": 52},
      {"name": "CD-2024", "category": "Connector", "serviceInterval": 208}
    ]}
  }
]}