Rule SQL Syntax

1. Basic SQL Syntax

SELECT /WHERE /FROM

SELECT field FROM event WHERE condition

• The FROM clause is used to select the topic or event source: for message publishing, enter the message topic; for events, enter the corresponding event topic.

• SELECT * selects all fields; you can also choose from the available fields.

  The output contains all available fields

  SELECT * FROM "$events/message_publish"

  Output with selected fields

  SELECT clientId as id, payload  FROM "$events/message_publish"

• WHERE applies conditional filtering to a field selected by SELECT.

  Select messages sent from the terminal with clientId= test001``

  SELECT * FROM "$events/message_publish"  where clientId = 'test001'

Operators

Function	Function purpose	Return value
+	Addition, or string concatenation	Sum, or the concatenated string
-	Subtraction	Difference
*	Multiplication	Product
/	Division	Quotient
=	Compares whether two values are exactly equal. Can be used to compare variables and topics	true/false
=~	Checks whether a topic can match a topic filter. Can only be used for topic matching	true/false
like	Same as MySQL's like syntax	Fuzzy query

Comparison Operators

Function	Function purpose	Return value
>	Greater than	true/false
<	Less than	true/false
<=	Less than or equal to	true/false
>=	Greater than or equal to	true/false
<>	Not equal to	true/false
!=	Not equal to	true/false

2. SQL Statement Examples

Basic Syntax Examples

• Extract all fields from messages whose topic is "/test01/post":

SELECT * FROM "/test01//post"

• Extract all fields from messages whose topic is "test01/a" or "test01/b":

SELECT * FROM "test01/a","test01/b"

• Extract all fields from messages whose topic matches 'test01/#'.

SELECT * FROM "test01/#"

• Extract the proto_name, payload, and clientid fields from messages whose topic matches 't/#':

SELECT proto_name, payload, clientId FROM "t/#"

• When a connection with clientid = 'c1' is established, extract its source IP address and port number:

SELECT peername as ip_port FROM "$events/client_connected" WHERE clientId = 'fastbee'

• Filter all clientids that subscribe to the 'test01/#' topic with a subscription level of QoS 1:

SELECT clientid FROM "$events/session_subscribed" WHERE topic = 't/#' and qos = 1

• Filter all clientids whose subscribed topic matches 't/#' with a subscription level of QoS 1. Note that unlike the previous example, this uses the topic matching operator '=~', so it also matches subscription events for 't' or 't/+/a':

SELECT clientid FROM "$events/session_subscribed" WHERE topic =~ 't/#' and qos = 1

• Filter all reported topics. Matches all topics containing 'test/post'; similar in function to '=~', like a SQL fuzzy match:

SELECT clientid FROM "$events/message_publish" WHERE topic like '%test/post%'

Notes

• Topics after the FROM clause must use double quotes "".
• The WHERE clause is followed by filter conditions; strings must be enclosed in single quotes ''.
• If there are multiple topics in the FROM clause, separate them with commas ",". For example, SELECT * FROM "test/1", "test/2".
• Do not create an alias for payload, otherwise it will affect runtime performance. That is, try not to write SELECT payload as py.