ForEach + PostgreSQL = POWERFUL OPTIONS
The For_Each_In( attribute_reference, big_nasty_postgres_statement ) Function
As the name suggests the For_Each_In function executes an instruction on every value in the returned value stream record set. A PostgreSQL instruction to be precise. Note that the original value is passed into the PostgreSQL expression as 'value'. So a simple "double everything" expression would look like this:
Example: for_each_in($.tank_temperature, 2 * value)
This is a powerful way to expose the rich set of PostgreSQL functions to the ThinkIQ platform. We can use this to cast strings to numeric attributes, which is useful when working with un-typed messenger protocols, such as mqtt.
Example: for_each_in($.string_value_from_mqtt, value::double precision)
Working with JSON Data
The ThinkIQ team has used time-stamped string attributes extensively to store multi-column records, or ticket-style data. Many of the functions discussed before focus on value stream attributes with numeric values, but there are a few functions that work particularly well with JSON string attributes.
Parsing JSON Strings
We found good use of the For_Each_in function in extracting properties from time stamped JSON string values. In the example below, we extract the netweight property from JSON values and return them as a double (Example 1) and string (Example 2).
Example 1: for_each_in($.abc_ticket, (value::jsonb->>'netweight')::double precision)
Exmaple 2: for_each_in($.abc_ticket, value::jsonb->>'productdestination')
The CASE WHEN THEN ELSE Function
This example combines parsing of JSON data with a basic IF statement. From time-stamped string values stored in the attribute "abc_ticket", we detect the once that have a property "productdestination" equal to "Dark Side", and from those we extract the "netweight" property, otherwise we return -1. This let's us use the trim function to remove the -1 values and only show the netweights of deliveries that are headed to the dark side.
Example: for_each_in( $.abc_ticket, CASE WHEN (value::jsonb->>'productdestination') = 'Dark Side' THEN (value::jsonb->>'netweight')::double precision ELSE -1 END)
Multiple CASE WHEN a) WHEN b) WHEN c) ELSE Function
This example shows how we can deal with multiple cases. Let's look at blinking lights in an industrial setting, andon's. The state is typically "off", "solid", or "blinking". Or something when terribly wrong. Let's convert those string states into integers that trend nicely.
Example:
for_each_in($.andon_state,
CASE
WHEN value = 'flashing' THEN 2
WHEN value = 'solid' THEN 1
WHEN value = 'off' THEN 0
ELSE -1
END
)