Common pitfalls

These examples use the events table from Load and query JSON.

Cast fields before comparing them

Comparison operators do not accept Variant values, so WHERE event:status >= 400 fails to plan. Cast the field to a SQL type first, as in Query.

Removing the need for the cast is tracked in #159.

Cast fields before grouping or joining on them

Grouping and join keys compare Variant values by their binary encoding, which includes the field names of the row each value came from. Equal values from rows with different fields do not match, so grouping by event:service splits one service into three groups:

SELECT event:service AS service, count(*) AS events
FROM events
GROUP BY event:service;

+---------+--------+
| service | events |
+---------+--------+
| "api"   | 1      |
| "api"   | 1      |
| "api"   | 1      |
+---------+--------+

A join on event:service matches no rows for the same reason. Cast the field to a SQL type first:

SELECT event:service::VARCHAR AS service, count(*) AS events
FROM events
GROUP BY event:service::VARCHAR;

+---------+--------+
| service | events |
+---------+--------+
| api     | 3      |
+---------+--------+

Making grouping and joins match equal Variant values is tracked in #183.

Tell SQL NULL from Variant null

A missing field returns SQL NULL. A JSON null is a Variant null value, which IS NULL does not match. Use is_variant_null to detect it:

SELECT event:status::BIGINT AS status,
       event:error IS NULL AS error_is_null,
       is_variant_null(event:error) AS error_is_variant_null
FROM events
ORDER BY status;

+--------+---------------+-----------------------+
| status | error_is_null | error_is_variant_null |
+--------+---------------+-----------------------+
| 200    | true          | false                 |
| 500    | false         | true                  |
| 504    | false         | false                 |
+--------+---------------+-----------------------+