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.

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

  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

  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)

  set categories = select distinct element.category from articles;
  

  Result:

  JSON

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

• Select all distinct service intervals lower than 200

  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}
    ]}
  }
]}