Filter Series with String Operators

The string comparison operators <, >, <=, >= allow you to filter tag values lexicographically.

If one of the compared values is NULL, the expression returns NULL and such rows are excluded by the WHERE clause.

NULL filter

Data

| entity  | datetime                 | value | tags.tag2 | > 'val2' | <= 'val2' |
|---------|--------------------------|-------|-----------|----------|-----------|
| e-sql-1 | 2016-06-19T11:00:00.000Z | 1.0   | null      | NULL     | NULL      |
| e-sql-2 | 2016-06-19T11:00:00.000Z | 2.0   | val2      | false    | true      |
| e-sql-3 | 2016-06-19T11:00:00.000Z | 3.0   | val3      | true     | false     |
| e-sql-4 | 2016-06-19T11:00:00.000Z | 4.0   | null      | NULL     | NULL      |

Query: greater comparator

SELECT datetime, value, tags.tag2
  FROM "m-metric1"
WHERE datetime >= '2016-06-19T11:00:00.000Z'
  AND tags.tag2 > 'val2'

Results

| datetime                 | value | tags.tag2 |
|--------------------------|-------|-----------|
| 2016-06-19T11:00:00.000Z | 3.0   | val3      |

Query: less than or equal comparator

Note that a series without a tag2 series tag, which is the same as a NULL value, are not included in either result set.

SELECT datetime, value, tags.tag2
  FROM "m-metric1"
WHERE datetime >= '2016-06-19T11:00:00.000Z'
  AND tags.tag2 <= 'val2'

Results

| datetime                 | value | tags.tag2 |
|--------------------------|-------|-----------|
| 2016-06-19T11:00:00.000Z | 2.0   | val2      |

Collation Filter

Strings are compared based on the Unicode value of their characters.

Data

| tags.'tag-1' | unicode-1 |
|--------------|-----------|
| null         | null      |
| .            | U+002E    |
| 01           | U+0030    |
| 1            | U+0031    |
| 10           | U+0031    |
| 11           | U+0031    |
| 2            | U+0032    |
| 20           | U+0032    |
| 3            | U+0033    |
| 30           | U+0033    |
| A            | U+0041    |
| AB           | U+0041    |
| B            | U+0042    |
| Resumes      | U+0052    |
| Résumé       | U+0052    |
| a            | U+0061    |
| a¨b          | U+0061    |
| resume       | U+0072    |
| resumes      | U+0072    |
| résumé       | U+0072    |
| résumés      | U+0072    |
| á            | U+00E1    |
| ä            | U+00E4    |
| äa           | U+00E4    |
| äb           | U+00E4    |
| äc           | U+00E4    |
| é            | U+00E9    |
| ÿ            | U+00FF    |
| ā            | U+0101    |
| ǎ            | U+01CE    |
| α            | U+03B1    |
| а            | U+0430    |

Query: >= to Filter by Character Unicode Value

SELECT tags.'tag-1', tags.'tag-unicode-1' AS "unicode-1"
  FROM "m-order"
WHERE entity = 'e-1'
  AND tags.'tag-1' >= 'ä'
ORDER BY tags.'tag-1' ASC

Results

| tags.'tag-1' | unicode-1 |
|--------------|-----------|
| ä            | U+00E4    |
| äa           | U+00E4    |
| äb           | U+00E4    |
| äc           | U+00E4    |
| é            | U+00E9    |
| ÿ            | U+00FF    |
| ā            | U+0101    |
| ǎ            | U+01CE    |
| α            | U+03B1    |
| а            | U+0430    |

Query: Numbers by Character Unicode Value if One of the Values is a Character

SELECT tags.'tag-1', tags.'tag-unicode-1' AS "unicode-1"
  FROM "m-order"
WHERE entity = 'e-1'
  AND tags.'tag-1' >= '0' AND tags.'tag-1' <= '3'
ORDER BY tags.'tag-1' ASC

Results

| tags.'tag-1' | unicode-1 |
|--------------|-----------|
| 01           | U+0030    |
| 1            | U+0031    |
| 10           | U+0031    |
| 11           | U+0031    |
| 2            | U+0032    |
| 20           | U+0032    |
| 3            | U+0033    |

Query: Numbers by Numeric Value if Both Values are Numeric

If all series tag values are guaranteed to be numeric or null, the values can be compared as numbers, however ordering of such columns is still based on an Unicode value (string collation).

Data

| entity | tags.'tag-1' | unicode-1 |
|--------|--------------|-----------|
| e-3    | 1            | U+0031    |
| e-3    | 10           | U+0031    |
| e-3    | 11           | U+0031    |
| e-3    | 2            | U+0032    |
| e-3    | 20           | U+0032    |
| e-3    | 3            | U+0033    |
| e-3    | 30           | U+0033    |

SELECT tags.'tag-1', tags.'tag-unicode-1' AS "unicode-1"
  FROM "m-order"
WHERE entity = 'e-3'
  AND tags.'tag-1' > 1 AND tags.'tag-1' <= 20
ORDER BY tags.'tag-1' ASC

Results

| tags.'tag-1' | unicode-1 |
|--------------|-----------|
| 10           | U+0031    |
| 11           | U+0031    |
| 2            | U+0032    |
| 20           | U+0032    |
| 3            | U+0033    |