Skip to content

Data Structures in ELEMENT

In ELEMENT IoT, these things have access to parts of the data structure:

  • Parsers (Elixir)
  • Rule conditions (Abacus)
  • Rule actions (Mustache)
  • Filters on the device list and readings list (AbacusSql)
  • Views (AbacusSql)

Accessing data in Abacus, AbacusSql and Mustache expressions

In Abacus, AbacusSql and Mustache expressions, the variables available depend on what data type is being processed. For example, if you want to filter some devices, the device's variables and associations are directly accessible.

When writing parsers with Elixir, the behaviour is a bit different and is documented in the parser tutorial.

To get the contents of a variable, just write down its name. Example: name (will return the name of the device)

To get associated data, such as the configured parser's name, you have to write down a path through the data structure to the parser and then call its name variable. In this case, this would just be parser.name, but if the data type being processed were a reading, it would be device.parser.name (device for the device that received the reading, parser for the parser of that device, name for the name of the parser).

In some cases an association is a list, in more technical terms, its cardinality is "many". In this case, you can access the n-th element using the [n] operator. For example, if you want to get the device EUI of a reading, use device.interfaces[0].opts.device_eui; that is device for the reading's device, interfaces for that device's interfaces list, [0] for the first interface, opts for the options map of that interface, device_eui for the EUI option.

Comparision operators (Abacus and AbacusSql)

When filtering data or deciding if a rule action should be executed, you will want to compare values with each other. For that, we offer comparision operators:

  • == for exact equality
  • != for inequality
  • a > b, which returns true if a is bigger than b
  • a < b, which returns true if a is smaller than b
  • >=, <=, the variants that also return true if both values are equal

Examples:

  • data.temperature >= 35 - return true when the a reading's temperature data is equal to or greater than 35
  • parser.name == "Super Sensor 3000" - return true when the device's parser has the name Super Sensor 3000

Boolean operators (Abacus and AbacusSql)

Sometimes you will want to link 2 conditions. For this, 3 boolean operators are available:

  • a && b - returns true only if both a and b are true
  • a || b - returns true when either or both a or b are true
  • not a - returns true when a is false

Examples:

  • data.door_open && data.brightness < 100 - returns true if a reading's door_open data is true and its brightness value is below 100

Mathematical operators (Abacus and AbacusSql)

The standard mathematical operators are available:

  • +, -, * and / for addition, subtraction, multiplication and division
  • ^ for exponential (Abacus only)
  • !n for the factorial (Abacus only)

Keywords in Abacus and AbacusSql

  • null - denotes a non-existant value
  • false and true - the 2 boolean values

Examples:

  • parser_id == null - devices that have no parser
  • meta.confirm == false - LoRaWAN packets that are unconfirmed

Functions (AbacusSql only)

In AbacusSql expressions, the following functions are available, they work exactly like their counterparts in SQL.

Conditional functions

Datetime functions

String functions

  • like and ilike, example: ilike(name, "%gateway%")
  • concat, example: concat(name, " - ", parser.name)
  • substr
  • to_hex, example: to_hex(511) == "1ff"
  • encode, example: encode(packet.payload, "hex")

Casts

Sometimes, values need to be converted to a different datatype. To do this, AbacusSql offers several functions.

Basic types

  • numeric - convert text or json values to numeric ones, this can be used for integer and floating numbers
  • text - convert boolean, numeric or json values to text
  • boolean - convert text, json values or 0 and 1 to boolean false and true

Use the cast functions like any other function:

  • numeric("3") - would return 3 and numeric("2.7") - would return 2.7
  • text(3) - would return "3"
  • boolean(0) - would return false

When using data from json fields in functions, it is required to cast them to one of text, numeric or boolean. A field is a json field, if any of its ancestors have the type of map or json.

UUIDs

When comparing UUID, a cast can be used:

  • device_id == uuid("a70a234e-d186-4ca8-836b-74816738939e")

Date and time types

  • timestamp and timestamptz - convert from any datetime type or from a string to a timestamp with or without time zone.
  • date - convert from any datetime type or from a string to a simple date
  • time and timetz - convert from any datetime type or from a string to a time with or without time zone
  • interval - convert a text to an interval. Read more on this in the PostgreSQL documentation for intervals
  • at_time_zone(input, timezone) - add or convert to the desired time zone. PostgreSQL documentation for AT TIME ZONE

The cast to interval is very useful when used in conjunction with a datetime field:

  • measured_at >= now() - interval("3 months") - an example filter that filters out any readings that are older than 3 months

Examples:

  • timestamptz("2018-05-02 19:30 Europe/Berlin") - results in 2018-05-02 17:30:00+00
  • timestamp("2018-05-02 19:30 Europe/Berlin") - results in 2018-05-02 19:30:00 (note that there is no offset in the time - the time zone has not been parsed)
  • timetz("15:30 MESZ") - results in 15:30:00+02
  • time("15:30 MESZ") - results in 15:30:00 (again, the time zone offset has not been parsed)
  • date("2018-03-19") - results in 2018-03-19
  • at_time_zone(timestamptz("2018-05-02 19:30Z"), "Europe/Berlin") - results in 2018-05-02 21:30:00 (note that a timestamp with time zone was shifted by 2 hours and then converted to a timestamp without time zone)
  • at_time_zone(timestamp("2018-05-02 19:30"), "Europe/Berlin") - results in 2018-05-02 19:30:00+02 (here, a timestamp without time zone is converted into one with time zone, and is not shifted by 2 hours)

Aggregations

Aggregation functions can be used in graphs and certain map layers. The following aggregation functions are available:

  • sum
  • avg
  • min and max
  • count