Writing rule query

release/20240425
English

Writing rule query

Article Summary

Share feedback

Thanks for sharing your feedback!

Available in Classic and VPC

Describes the built-in function that can be used for writing rule query as well as the rules for writing rule query.

Rule query writing rules

Write a particular rule query for message content sent from the IoT device. To write a rule query, you must understand the basic SQL phrases. In addition, only the SELECT phrases are supported, so you need to write in the order of SELECT - FROM - WHERE.

The rule query writing rules are as follows:

SELECT [<토픽 Alias>.<메시지 JSON Key1>, <토픽 Alias>.<메시지 JSON Key2>, ...] FROM "[토픽]" AS [토픽 Alias] WHERE [조건]

The rule query writing examples are as follows:

<Example 1>

Message structure

{
  "deviceId": "1",
  "deviceType": "illumination",
  "buildingId": "N1001",
  "roomId": "001",
  "light": 75,
  "battery": 3.3,
  "eventTime": "2020-01-01 00:00:00"
}

Query that extracts all fields in the message of data with illumination brightness of less than 50 lux
```
SELECT * FROM "apt/+/sensor/light" AS t WHERE t.light < 50
```
Query that extracts only a specific field in the message of data with the specific building's illumination brightness of less than 50 lux
```
SELECT t.light, t.buildingId, t.roomId, t.eventTime FROM "apt/#" AS t WHERE t.buildingId = 'N1001' AND t.light < 50
```

<Example 2>

Message structure

{
  "deviceId": "device_1",
  "deviceType": "temperature",
  "value": 35,
  "battery": 9,
  "date": "2016-12-15",
  "time": "15:12:00"
}

Query for inspecting cases where the remaining battery value is less than 10%
```
SELECT * FROM "factory/room1/temperature" AS t WHERE t.battery < 10
```
Query that extracts only a specific field such as an ID value (deviceid) and battery value of a device that can be classified
```
SELECT t.deviceId, t.battery FROM "factory/room1/temperature" AS t WHERE t.battery < 10
```

Note

The maximum topic length of rule query FROM is 255 bytes (UTF-8 encoding) and up to Step 7 can be used.

SELECT/WHERE clause

The description on the SELECT/WHERE clause is as follows:

It can be written similarly to the general SQL query, and can use an * (asterisk) like SELECT * FROM.
Multiple data processing queries such as GROUP BY / ORDER BY / DISTINCT / JOIN / UNION / UNION ALL / INTERSECT / LIMIT aren't supported.
SUBQUERY isn't supported.
You can select a specific key value as a query result by using a key (e.g., light, buildingId, ...) of a JSON message transmitted to MQTT.
If you don't use JSON field keys such as built-in functions or arithmetic expressions as they are, use an Alias to specify the JSON field key to be used as the result value. If there is no Alias, then the JSON field key of the query result is output as _1, _2, ..., _n according to the position in the SELECT clause.

<Example>

Query

SELECT t.productId, t.weight AS gram, t.weight / 1000.0, t.weight * 0.0352, t.weight / 1000.0 AS kg,  t.weight * 0.0352 AS oz FROM "scm/warehouse/001" AS t

Results
t.weight / 1000.0 and t.weight * 0.0352 have no Alias, so the 3rd position of the SELECT clause is displayed as "_3," and the 4th position is displayed as "_4."
```
{
"productId":"23",
"gram":1500,
"_3":1.5,
"_4":52.8,
"kg":1.5,
"oz":52.8
}
```

Note

For a description on the rule query built-in function supported by Cloud IoT Core, see Rule query built-in function.

FROM clause

The following describes the FROM clause.

Only one MQTT topic is available.
The maximum length of the topic name is 255 characters.
A topic must be enclosed with " (double quotes).
If there is a JSON field key that is the same as the topic name in the SELECT or WHERE clause, then the topic must specify the Alias. It must be written in the format [Topic Alias].[JSON Field Key].
Topic levels should be separated by a slash (/).
Topics can use English characters, numbers, spaces, and characters of !@$%^&()_-={[}]?></`’.
. (period), * (asterisk), and " (double quotes) can't be included in the topic.
The topic can't be / or //.
A multilevel wildcard, # (hashtag), can only be used in the final level.
A single level wildcard, + (plus), can only be used in all levels.
The wildcard must be used solely on the relevant level, and cannot be used along with any other character.
If there is a republish message action that is already registered in the rule, then it can't be set as a topic that can become the rule query again by the republish message action.

String

When using a string in a function or comparison statement, it must be enclosed with ' (single quotes).

<Example>
Comparison of the deviceType field value and the string sensor in a JSON message

SELECT * FROM "factory/room1/temparature" WHERE deviceType = 'sensor'

JSON field key

If a JSON field key is used in the query, but there is no corresponding JSON field key in the input message, then a query error occurs. Query errors can be checked by error actions.

<Example>
An error occurs since there is no value in the device and volt fields in a JSON message.

SELECT device FROM "factory/room1/temparature" WHERE volt = 1.5

{
  "lux": 1
}

You can clearly show that the JSON field key is being used by using " (double quotes). In the next case, the field key must be enclosed with " (double quotes).

When you have to classify capital letters and small letters of the JSON field key, and the JSON message is as below, the deviceType does not classify the capital letters and the small letters in the query, so if you do not enclose it with " (double quotes), an error occurs because it cannot be known which JSON field key must be selected.
```
{
  "deviceType": 10,"DEVICEType": 5
}
```
```
SELECT deviceType FROM "factory/room1/temparature" WHERE deviceType = 10
```
```
  Multiple matches were found for the specified identifier Evaluator Error
```
When the JSON field key is a word used as the keyword and if the JSON field key is the same as a reserved keyword that cannot be used in a query statement when a rule query is being verified, the following error occurs. To use a JSON field key which is the same as the keyword, you must specify that it is a JSON field key by enclosing it with " (double quotes).
```
Unexpected keyword
```
"time" is used in order to use the "time" JSON field key since time is the keyword in the query statement.
```
SELECT "time" FROM  "factory/room1/temparature"
```
When . (period) is included in the JSON field key, or in case of a JSON field key including a special character, the . (period) of the field in a rule query means an overlap field within a JSON message. If the JSON field key contains . (period), it can be treated as a special character in the field key value by enclosing it with " (double quotes).

<Example>

Message content

{
"deviceId": "1",
"deviceType": "sensor",
"temperature": 21.3,
"device.serialNo": 100001,
"manufacture": {
  "company": "myCompany",
  "buildDate": "2020-01-01"
}
}

Look up nested fields

SELECT manufacture.company FROM "factory/room1/temparature"

Look up fields containing . (period)

SELECT "device.serialNo" FROM "factory/room1/temparature"

When there is a JSON field key containing special characters

SELECT * FROM "factory/room1/temparature" WHERE "deviceType@number" = "sensor"

Rule query built-in function

Describes the built-in function that can be usefully used for writing a rule query. The name of a built-in function can be inputted regardless of the capital letters and small letters. The built-in function should be explicitly displayed as the key value of JSON, which is the query result value using Alias. If you input the "function name (input argument)", the value is returned. The return value types are Number (integer or decimal), Integer, String, Bool (true or false logic value), Any (regardless of type).

The following shows how to write.

SELECT ABS(volt) AS volt FROM "topic"

Note

In the example of each built-in function below, a constant value is entered for the input argument. When the key value of the input JSON message is entered, however, the JSON Value is evaluated.
The argument value of the built-in function can be a constant number. In the case of a constant value string, enclose it with ' (single quotes); set the bool value as true or false, and enter the JSON value enclosed with ` (grave accents).
If the number of input arguments of built-in function is different or the input argument types are different, the query fails. You can check the reason why the query has failed by adding an error action.

ABS

Returns the absolute value.

Statement
```
ABS(Number)=Number
```
Examples
```
ABS(-10)=10
```

SIGN

Returns the sign. Returns the sign 1 if the sign is +, -1 for -, and 0 for 0.

Statement
```
SIGN(Number)=Number
```
Examples
```
SIGN(-5)=-1, SIGN(5)=1, SIGN(0)=0
```

CEIL

Returns the value rounded up to the nearest integer.

Statement
```
CEIL(Number)=Integer
```
Examples
```
CEIL(1.1)=2, CEIL(-1.1)=-1
```

FLOOR

Rounds down to the nearest whole number.

Statement
```
FLOOR(Number)=Integer   
```
Examples
```
FLOOR(1.8)=1, FLOOR(-1.8)=-2
```

ROUND

Returns the value rounded off to the nearest whole number.

Statement
```
ROUND(Number)=Integer
```
Examples
```
ROUND(0.5)=1, ROUND(-1.5)=-2
```

TRUNC

Returns the value obtained by cutting the decimals as the number of the second argument. When the second argument is a negative number, it rounds down 0, and when the second argument is a decimal, it rounds down the decimal points, and the second argument is substituted.

Statement
```
TRUNC(Number, Number)=Number
```

Examples

TRUNC(3.3, 0)=3, TRUNC(3.33312, 2)=3.33, TRUNC(3.000, 2)=3, TRUNC(2.23, -2)=2, TRUNC(2.23, 1.5)=2.2

MOD

Returns the remainder after dividing the first argument by the second argument. You can now use the % operator.

Statement
```
MOD(Integer, Integer)=Integer
```
Examples
```
MOD(5, 2)=1
```

SQRT

Returns the square root of a number.

Statement
```
SQRT(Number)=Number
```
Examples
```
SQRT(1.44)=1.2
```

POWER

Return the result of involuting the first argument by the second argument.

Statement
```
POWER(Number, Number)=Number
```
Examples
```
POWER(2, 4)=16
```

LOG

Return the Log value with the value of the first argument as the base of the second argument. When the first argument is 0, return -inf String, and if the first argument is less than 0, return nan String. If the second argument is equal to or smaller than 0, and if it is equal to 1, return nan String.

Statement
```
LOG(Number, Number)=Number
```
Examples
```
LOG(100, 10)=2
```

EXP

Returns the power of a number entered as e (natural constant, Euler's constant).

Statement
```
EXP(Number)=Number
```
Examples
```
EXP(1)=2.718281828459045
```

LN

Returns the log value of the number entered with e (natural constant, Euler's constant) as the base. If the number is smaller than 0, return nan, and if it is 0, return -inf String.

Statement
```
LN(Number)=Number
```
Examples
```
LN(10)=2.302585092994046
```

Trigonometric function

Returns the trigonometric function value. The available trigonometric functions are SIN(n), SINH(n) COS(n), COSH(n), TAN(n), TANH(n), ASIN(n), ACOS(n), ATAN(n), ATAN2(n), and ATANH(n).

Statement
```
Trigonometric function(Number)=Number
```

BITOR

Converts 2 values into bit values and returns the OR-operated value as decimal numbers.

Statement
```
BITOR(Integer, Integer)=Integer
```
Examples
```
BITOR(4, 2)=6
```

BITAND

Converts two values into bit values and returns the AND-operated value in decimal numbers.

Statement
```
BITAND(Integer, Integer)=Integer
```
Examples
```
BITAND(4, 2)=0
```

BITXOR

Converts 2 values into bit values and returns the XOR-operated value in decimal numbers.

Statement
```
BITXOR(Integer, Integer)=Integer
```
Examples
```
BITXOR(5, 1)=4
```

NANVL

If the first argument is a number, then the first argument is returned; if the first argument isn't a number, then the second argument is returned.

Statement
```
NANVL(Any, Any)=Any
```

Examples

NANVL(1.1, 1.5)=1.1, NANVL('STRING', '3')="3"

ISNULL

If the value is null, it returns true. Otherwise, it returns false.

Statement
```
ISNULL(Any)=Bool
```

Examples

ISNULL("not null")=false, ISNULL(null)=true

NULLIF

If the input two argument values are the same, then it returns null; if they are different, then the first argument value is returned.

Statement
```
NULLIF(Any, Any)=Any
```
Example 1
```
NULLIF(1, 1)=null
```
Example 2 - If the value of deviceId is null, return null. Otherwise, return the value of deviceId.
```
NULLIF(deviceId, null)
```

Example 3

SELECT NULLIF(deviceId, null) AS deviceId FROM "topic" WHERE member IS NOT NULL

Example 4

SELECT NULLIF(deviceId, '2x33sed') AS deviceId FROM "topic"

EXISTS

Check if there is an item of JSON list or JSON object. Returns true if present. If not, returns false.

Statement
```
EXISTS(Any)=Bool
```
Example 1 - Return false since it is not JSON list or JSON object.
```
EXISTS(5)=false 
```

Example 2

Input JSON

  { "device" : [] } or { "device" : {} }

Query

SELECT EXISTS(device) as empty_device FROM "iot"

Result JSON
```
  { "empty_device" : false }
```

RAND

Returns a random value with an even probability in the range of 0.0 - 1.0.

Statement
```
RAND()=Number
```
Examples
```
RAND()=0.1897250037492607
```

CHAR_LENGTH

Returns the number of characters in a string.

Statement
```
CHAR_LENGTH(String)=Integer
```
Examples
```
CHAR_LENGTH('heLLo')=5
```

UPPER

Returns with uppercase letters. Non-alphabetic letters aren't affected.

Statement
```
UPPER(String)=String
```
Examples
```
UPPER('hi99')="HI99"
```

LOWER

Returns with lowercase letters. Non-alphabetic letters aren't affected.

Statement
```
LOWER(String)=String
```
Examples
```
LOWER('NEXT99')="next99"
```

TRIM

Removes certain characters before or after a string. It can be used in various forms such as TRIM (String), TRIM (expression FROM String), and TRIM (expression String FROM String).

For LEADING, only the beginning of a string is removed. For TRAILING, only the end of a string is removed. For BOTH, both the beginning and end are removed.
Basically, you can specify a specific character between LEADING, TRAILING, and BOTH to remove that character. If this isn't specified, then spaces are removed.

Statement

TRIM(expression String FROM String)=String

Examples

- TRIM('   foobar   ')                  -- "foobar"
- TRIM('   \tfoobar\t    ')             -- "\tfoobar\t"
- TRIM(LEADING FROM '    foobar    ')   -- "foobar    "
- TRIM(TRAILING FROM '    foobar    ')  -- "    foobar"
- TRIM(BOTH FROM '    foobar    ')     -- "foobar"
- TRIM(BOTH '1' FROM '11foobar11')             -- "foobar"
- TRIM(BOTH '12' FROM '1112211foobar22211122') -- "foobar"

LEADING_PAD

Adds a specific string to the beginning of the string.

Statement

LEADING_PAD(String, Integer, String(Option))=String

Examples

LEADING_PAD('device', 2)="&nbsp;&nbsp;device", LEADING_PAD('device', 2, 'm')="mmdevice"

TRAILING_PAD

Adds a specific string to the end of the string.

Statement

TRAILING_PAD(String, Integer, String(Option))=String

Examples

TRAILING_PAD('device', 2)="device&nbsp;&nbsp;",         TRAILING_PAD('device', 2, 'm')="devicemm"

NUMBYTES

Returns the number of bytes in UTF-8 encoding in a string.

Statement
```
NUMBYTES(String)=Integer
```
Examples
```
NUMBYTES('good')=4, NUMBYTES('∀')=3
```

STARTSWITH

Returns true or false value to signify whether the first argument starts with the second argument's string.

Statement
```
STARTSWITH(String, String)=Bool
```
Examples
```
STARTSWITH('hello', 'he')=true
```

ENDSWITH

Returns true or false value to signify whether the first argument ends with the second argument's string.

Statement
```
ENDSWITH(String, String)=Bool
```
Examples
```
ENDSWITH('hello', 'lo')=true
```

INDEXOF

If the first argument contains the second argument's string, then it returns the starting index of the same first string. If not, it returns -1.

Statement
```
INDEXOF(String, String)=Integer
```

Examples

INDEXOF('hello', 'el')=1, INDEXOF('hello', 'xx')=-1

REPLACE

Finds the string of the second argument from the string of the first argument and returns all of them with the third argument.

Statement
```
REPLACE(String, String, String)=String
```
Examples
```
REPLACE('hello', 'l', 'ee')="heeeeeo"
```

REGEXP_REPLACE

Finds all the strings that match the second regular expression in the string of the first argument and returns them all with the third argument. You can use "$" for the third argument and refer to a capture group.

Statement

REGEXP_REPLACE(String, String, String)=String

Example 1

REGEXP_REPLACE('xdevice', 'x{.*}', 'invalid')="invalid"

Example 2

REGEXP_REPLACE('x is b y is a', '(.*) is (.*))', '$2')="a"

REGEXP_MATCHES

Returns true value if the first argument's string contains a string that matches the second regular expression.

Statement
```
REGEXP_MATCHES(String, String)=Bool
```

Examples

REGEXP_MATCHES('hello', 'l{2,}')=true, REGEXP_MATCHES('halo', 'l{2,}')=false

REGEXP_SUBSTR

Finds the first matching string in the string of the first argument that matches the second regular expression.

Statement
```
REGEXP_SUBSTR(String, String)=String
```

Examples

REGEXP_SUBSTR('byebyeY', '(bye)*')="byebye"

CONCAT

Connects the STRING values of all arguments.

Statement
```
CONCAT(String, String, ...)=String
```
Examples
```
CONCAT('he', 'll', 'o')="hello"
```

LIST

Returns the values of all arguments as List.

Statement
```
LIST(Any, Any, ...)=JSONList
```

Examples

LIST('he', 1, true, null, `{"a":"b"}`,`[1]`)=["he", 1, true, null, {"a":"b"}, [1,2,3]]

FLATLIST

Returns the values of all arguments as List. If there is a list in the argument, release it and add it.

Statement
```
FLATLIST(Any, Any, ...)=JSONList
```

Examples

LIST('he', 1, true, null, `{"a":"b"}`,`[1,2,3]`)=["he", 1, true, null, {"a":"b"}, 1, 2, 3]

SUBSTRING

Cuts the string. Return the string for the number of the third argument value from the character falling under the number of the second argument value in the string of the first argument value. If there is no third argument, all strings after the character falling under the number of the second argument are returned.

Statement

SUBSTRING(String, Integer, Integer)=String

Examples

- SUBSTRING('12345', 0)="12345", SUBSTRING('12345', 1)="12345", SUBSTRING('12345', 3)="345"
- SUBSTRING('12345', 2, 3)="2", SUBSTRING('12345', -2, 0)="" (empty value) , SUBSTRING('12345', 5, 5)=""(empty)

NEWUUID

Returns a random 16-byte UUID.

Statement
```
NEWUUID()=String
```

Examples

NEWUUID()="0491e6b5-afee-4a39-97a3-14bc6c35fda9"

HASH

Obtains the hash of the first argument in the type of the second argument. The supported hash types are MD2, MD5, SHA-1, SHA-224, SHA-384, SHA-256, and SHA-512. Capital letters and small letters are not classified, and the correct type must be entered.

Statement
```
HASH(String, String)=String
```

Examples

HASH('iotgood', 'sha-1')="edcf3d81bd181b9b37bc9748766b16345f7fb405"

ENCODE

Encodes the first argument in the encoding type of the second argument. The supported encoding type is Base64, but you must enter the correct type.

Statement
```
ENCODE(String, String)=String
```
Examples
```
ENCODE('iot', 'base64')="aW90"
```

DECODE

Decodes the first argument in the decoding type of the second argument. Base64 is the supported decoding type, and the correct type must be entered.

Statement
```
DECODE(String, String)=String
```
Examples
```
DECODE('aW90', 'base64')="iot"
```

CHR

Returns the ASCII character corresponding to the entered Integer.

Statement
```
CHR(Integer)=String
```
Examples
```
CHR(65)="A"
```

GET_ITEM

Imports the item of the order of the second argument from the first argument value. If the first argument is String, then one character of the Index is imported.

Statement

GET_ITEM(JSONObject or JSONList or String)=Any

Examples

Import from List

GET_ITEM(`["banana", "apple", "grape"]`, 2)="grape"

Import from Object

GET_ITEM(`{"device":"none"}`, 'device')="none"

Import from String

GET_ITEM('banana', 2)="n"

SIZE

Returns the size of a JSON value.

Statement
```
SIZE(JSONObject or JSONList)=Integer
```

Example 1: Returns the number of items in a list

Input JSON
```
{
  "lux": [null, 2, "3"]
}
```

Query

SELECT SIZE(t.lux) AS "count" FROM "topic" AS t

Result JSON
```
{
  "count": 3
}
```

Example 2: Returns the number of key-value pairs

Input JSON

{
  "info": {
    "dv1": 1,
    "volt": "1v",
    "id": null
  },
  "lux": [1, 2]
}

Query

SELECT SIZE(t.info) AS "count" FROM "topic" AS t

Result JSON
```
{
  "count": 3
}
```

Example 3: When entering the JSON value directly, it can be entered by enclosing it with ` (Grave Accent).
- Query
```
SELECT SIZE(`{ "device": 1, "lux": 1}`) AS "count" FROM "topic" AS t
```
- Result JSON
```
{
  "count": 2
}
```

COALESCE

Returns the value of the leftmost argument among non-null values among arguments. The input argument is variable, and at least one must be entered.

Statement
```
COALESCE(...)=Any
```

Examples

COALESCE('s', null, 1)='s', COALESCE(null, null, 1)=1,  COALESCE(null, null)=null

CAST

Converts the type of the value. Type can be boolean, integer, float, or string.

Statement
```
CAST(Any AS type)=type
```

Example 1: Converts the type into Boolean (Only 1, 1.0, 'true' are converted to true. Otherwise, false.)

- CAST(0 AS boolean)=false
- CAST(1 AS boolean)=true
- CAST(1.1 AS boolean)=true
- CAST('1' AS boolean)=false
- CAST('true' AS boolean)=true

Example 2

- CAST(true AS integer)=1
- CAST(false AS integer)=0
- CAST(1.75 AS integer)=1
- CAST('aa' AS integer)=(error occurs)
- CAST('22' AS integer)=22

Example 3

- CAST('2.2' AS float)=2.2
- CAST(true AS float)=1.0
- CAST(true  AS string)="true"

UNIXTIME_NOW

Returns the Unix timestamp of the current time in milliseconds.

Statement
```
UNIXTIME_NOW()=Integer
```
Examples
```
UNIXTIME_NOW()=1594614865656
```

UNIXTIME_TO_STRING

Converts a string by converting the first argument according to the date format of the second argument. The third argument is TimeZone. If there is no value, then it is converted to meet the criteria of the UTC TimeZone.

Statement

UNIXTIME_TO_STRING(Integer, String, String(Option))=String

Example 1

UNIXTIME_TO_STRING(1594579192000, 'yyyy MM dd hh:mm:ss z', 'Asia/Seoul')="2020 07 13 03:39:52 KST"

Example 2

UNIXTIME_TO_STRING(1594579192000, 'hh:mm:ss')="06:39:52"

STRING_TO_UNIXTIME

Converts the string into a Unix timestamp (milliseconds) according to the date format of the first argument to the second argument. The third argument is time zone. If there is no value, then it is converted to the UTC TimeZone.

Statement

STRING_TO_UNIXTIME(String, String, String(Option))=Integer

Example 1

STRING_TO_UNIXTIME('2020 07 13 03:39:52', 'yyyy MM dd hh:mm:ss', 'Asia/Seoul')=1594579192000

Example 2

STRING_TO_UNIXTIME('2020 07 13 06:39:52.222', 'yyyy MM dd hh:mm:ss.SSS')=1594622392222

EXTRACT

You can get YEAR, MONTH, HOUR, MINUTE, DAY, and SECOND values in UTC time zone by using expressions on Unix timestamps.

Statement

EXTRACT(expression FROM Integer)=Integer

Examples

- EXTRACT(YEAR FROM 1594579192031)                           -- 2020
- EXTRACT(MONTH FROM 1594579192031)                          -- 7
- EXTRACT(HOUR FROM 1594579192031)                           -- 18
- EXTRACT(DAY FROM 1594579192031)                            -- 12
- EXTRACT(MINUTE FROM 1594579192031)                         -- 39
- EXTRACT(SECOND FROM 1594579192031)                         -- 52

ADD_TIME

You can add or subtract YEAR, MONTH, HOUR, MINUTE, DAY, and SECOND values in UTC time zone by using expressions on Unix timestamps. Adds the second argument value to the third argument value by the unit of expression.

Statement

ADD_TIME(expression, Integer, Integer)=Integer

Examples

ADD_TIME(MINUTE, -10, 1594579198031)=1594578598031

DIFF_TIME

You can use an expression on a Unix timestamp to get the difference in time based on YEAR, MONTH, HOUR, MINUTE, DAY, and SECOND. If the Unix timestamp of the second argument is greater than the Unix timestamp of the third argument, the returned value appears as -.

Statement

DIFF_TIME(expression, Integer, Integer)=Integer

Example 1

DIFF_TIME(SECOND, 1594579192031, 1594579198031)=6

Example 2

DIFF_TIME(MINUTE, 1594579992031, 1594579198031)=-13

CASE statement

You can do branch processing like a switch by using the CASE statement. One or more WHEN clauses are required (ELSE clause is optional). In WHEN, it is possible to only check whether the evaluation values are the same or not, but inequality signs can't be used.

Example 1

CASE evaluation value WHEN value1 THEN return value1
WHEN value2 THEN return value2 ... 
ELSE return value remainder END

Example 2

Input JSON

  { "fruit" : "apple" }

Query

SELECT CASE fruit WHEN 'apple' THEN 'red' WHEN 'banana' THEN 'yellow' ELSE 'no color' END as color FROM "fruit/color"

Result JSON

  { "color": "red" }

Example 3: For evaluation value and value1, value2... both number and string are possible, and you can refer to the JSON value as key value.

Input JSON

  { "my_score" : 31, "team_score" : 30 }

Query

SELECT CASE 30 WHEN my_score THEN my_score * 2 WHEN team_score THEN team_score/2 ELSE 'nothing 30' END as final_score FROM "score/30"

Result JSON

  { "final_score" : 15 }

Literals statement

You can write your own literal values.

Example 1

Input JSON

  { "xy": [47.606,-122.332] }

Query

SELECT {'x': GET_ITEM(xy, 0),'y':GET_ITEM(xy, 1)} as result FROM "topic/coord"

Result JSON

  { "result" : { "x" : 47.606, "y" : -122.332 } }

Example 2

Input JSON

   {"lat" : 47.696, "long" : -122.332}

Query

SELECT [lat,long] as lat_long FROM "topic/coord"

Result JSON

  { "lat_long" : [47.606,-122.332]}

Was this article helpful?

What's Next

Connecting IoT devices

Table of contents

Rule query writing rules
Rule query built-in function
CASE statement
Literals statement