SELECT FROM WHERE Macro Syntax¶

The types of return values are indicated by the syntax:

{{SELECT FROM | WHERE}} for single values or objects
{%SELECT FROM | WHERE%} for dictionaries
{#SELECT FROM | WHERE#} for lists

The SELECT FROM part is a model reference and uses dot notation.

For lists and dictionaries, the SELECT FROM notation can contain a wild card asterisk * to return all matching values.

Examples returning all User attributes:

{# data.User.* |username: js54321, last_name: 'van Dever' #}
{% data.User.* |username: js54321 %}

The WHERE part is one or more comma-separated name:value pairs of attribute values of the model reference. If the value itself has a comma, it should be wrapped in quotes.

For example:

{{ data.Address.*  | street_name: 'The Willows, Park Crescent' }}

The value can also contain:

a reference to an empty field, as in:
```
{# data.User.* | email: null #}
```
a reference to a defined macro used to return a value, as in:
```
{# data.DRTPBXMeta.* | PBX:macro.getHost #}
```
a boolean value using the corresponding macro function, as in:
```
{{ data.DATA1.name | code: fn.true }}
```

The WHERE part also supports:

Asterisk * for filtering.

Examples:
```
{{ device.cucm.Phone.lines.line | name: "SEPD13B004F0719",
   lines.line.*.dirn.pattern:"1006",
   lines.line.*.dirn.routePartitionName:
    "AllowEmerCalls-NewSite4" }}
```
If there is a * in the WHERE clause:
- the results list is reduced with that specific clause
- the specific WHERE clause is a list
Note that only one * is supported and if the WHERE clause has an invalid field name, it will be ignored and still return the data.
regular expression type syntax:

field:/regex/

Note: regular expression syntax should not be used when querying collections with more than 500 members.

Examples:
```
{# data.Countries.country_name | country_name:/ia$/ #}
```
returns names that end in “ia”:
```
[ "Australia", "Saudi Arabia" ]
```
To exclude a list of countries matching “ia” in the name:
```
{# data.Countries.country_name | country_name:/[^ia]$/ #}
```
To carry out a case-insensitive search, add the regex parameter:
```
{# data.UserSavedSearch | username: /input.username/i #}
```
Will for example match:
```
[email protected]
[email protected]
[email protected]
```
Macros cannot be nested in the regex. This will not work:
```
{# data.Countries.* | iso_country_code: /[^({{input.ISO}})]/ #}
```
But this will work:

Macro ISO_REGEX:
```
/[^({{input.ISO}})]/
```
Macro:
```
{# data.Countries.* | iso_country_code:macro.ISO_REGEX #}
```
attributes not in the data of the model schema
- __pkid
- __device_pkid
- __bkey
- __hierarchy
- __hierarchy_friendly_path
- __hierarchy_friendly_parent_path
Examples:
- Given the following macro USA_pkid:
```
{{ data.Countries.__pkid | iso_country_code:USA }}
```
  then macro:
```
{{ data.Countries.country_name | __pkid:macro.USA_pkid }}
```
  will return:
```
{"United States of America"}
```
- Using __device_pkid:
```
{{ device.cucm.CallManager.__device_pkid | name:CM_sol-cucm-ob-01}}
```
  returns:
```
5c6f4ad0de894e0014e5b84c
```
Note that when using __hierarchy_friendly_path in a WHERE clause, the option clause direction will be ignored, for example:
```
{# data.Countries.* | __hierarchy_friendly_path: sys.TestMacros #}
```
will only return Countries at this hierarchy node.

For more details on this parameter, refer to the topic Macro Syntax to Filter by Meta properties.

The SELECT-FROM-WHERE macros can also take additional filter parameters that restrict results:

| direction:[up|down|local|parent|below|above|fulltree]

| device:[pkid of device]

| ndl:[pkid of ndl]

| limit:[number]

| skip:[number]

| title:[character]

The direction option can be added to return values relative to the current hierarchy position. The default direction is down:

direction:up - Upwards. Include current hierarchy.
direction:down - Downwards. Include current hierarchy.
direction:local: - On this level only. Include current hierarchy.
direction:parent - Parent only. Exclude current hierarchy, in other words, search the parent as local.
direction:below - Downwards. Exclude current hierarchy.
direction:above - Upwards. Exclude current hierarchy if current hierarchy is not top (sys).

This is the default hierarchy search result restriction if no direction option is specified.
direction:fulltree - Upwards (up) and Downwards (down) from the current hierarchy. Allows for a full hierarchy search.

It is recommended to use a direction option in a list macro without a WHERE clause, for example:

{# data.Countries.* || direction:up #}

In a ‘SELECT-FROM-WHERE’-type macro, a single bar indicates the direction, for example:

{{ data.Countries | iso_country_code:AUS | direction:up }}

If used in other macro types, a double bar is used for parameters, for example:

{# data.SiteDefaultsDoc.defaultcucphonesystem || direction:local #}

When traversal is up or above, results will be ordered starting with ones at the lowest hierarchies. Otherwise, results will be ordered starting with the ones at the highest hierarchies. Results at the same hierarchy will be in arbitrary order. Also refer to the optional to specifier below.

Added to the direction option is:

an optional limit specifier. When used, the results returned by a list comprehension will be limited to the specified count, for example:
```
{# data.test_user.name || direction:above,limit:2 #}
```
This will return the first two names of data/test_user instances at the closest ancestors.

By default, for the following filter specifiers values apply to lists if they are not present:
- skip (default: 0) - skip over a number of values before listing
- limit (default: 2000) - number of values to return in the list
So, if the first list macro was:
```
{# data.test_user.name || skip:0,limit:2000 #}
```
then the second batch of results can be obtained by:
```
{# data.test_user.name || skip:2000,limit:2000 #}
```
an optional to specifier to restrict the direction:up or direction:down specifier to a specified hierarchy, for example:
```
{# data.Countries.* || direction:up,to:Customer #}
```
This example will restrict the search upwards up to the Customer hierarchy.
```
{# data.Countries.* || direction:down,to:Site #}
```
This example will restrict the search downwards up to the Site hierarchy.

Valid hierarchy values for to: are:
- System
- Hcs
- Provider
- Reseller
- Customer
- IntermediateNode
- Site
- LinkedSite
All data is returned in the case of incorrect spelling or invalid hierarchy levels.

Example showing query results:

If a list macro is used to provide a list of input values on the GUI, note that custom values are permanently allowed. This means that a custom value can be entered in the GUI input. Adding a GUI rule on the GUI form that sets an object property attribute “Allow Custom Values” to false, will not affect this functionality.

In addition, a title filter can be applied if the SELECT-FROM query is for a string to only return values matching its value, with a regular expression, for example:

{# data.Countries.country_name || title:.*a$ #}

returns:

[
 "Australia",
 "Canada",
 "China",
 "Saudi Arabia",
 "South Africa",
 "United States of America"
]

Device option:

A device or ndl (Network Device List) pkid can be specified to restrict a query, for example, assume a named macro MY_CUCM_PKID_150:

{{ data.CallManager.__pkid | host:172.29.248.150,port:8443 }}

then we can select from the specified device as follows:

{{ device.cucm.HuntList.__pkid | name: "DR-Test1" | device:macro.MY_CUCM_PKID_150 }}

{{ device.cucm.HuntList.__hierarchy | name: "DR-Test1" | device:macro.MY_CUCM_PKID_150 }}

{{ device.cucm.HuntList.__hierarchy_friendly_path | name: "DR-Test1" |
  device:macro.MY_CUCM_PKID_150 }}

{{ device.cucm.HuntList.__hierarchy_friendly_parent_path | name: "DR-Test1" |
  device:macro.MY_CUCM_PKID_150 }}

In a GUI Rule, [ and ] indicate references to values in the current usage context of the GUI Rule if the macro is added as a Value to the GUI Rule.

A GUI rule Action can also have an API call as its Source. The references are current context hierarchy pkid’s or to field attribute names in the WHERE section of SELECT FROM WHERE-type macros - enclosed in square brackets [ ].

For example:

/api/tool/Macro/?method=evaluate&hierarchy=[hierarchy]&input={{Countries.iso_country_code |
   country_name:[countries.name]}}

The syntax in a GUI Rule for a Wizard also uses [], in the format [stepData.STEPNAME.ATTRIBUTE], for example:

[stepData.SubscriberType.role]

VOSS Automate 21.3

SELECT FROM WHERE Macro Syntax¶