Basic Usage
Range queries are supported over JSON fields indexed as
fast.
The following code block executes a range query over a JSON field:
SELECT description, metadata
FROM mock_items
WHERE id @@@ paradedb.range(
'metadata.attributes.quantity',
int4range(2, 5, '()')
);
Datetime Values
Datetime values stored inside JSON must be RFC 3339 formatted in order to work with range queries. The following values
are RFC 3339 compliant:
-- UTC
2024-10-06T14:30:00Z
-- Timezone offset
2024-10-06T14:30:00+02:00
-- Fractional seconds
2024-10-06T14:30:00.456Z
The following values are not RFC 3339 compliant:
-- No timezone
2024-10-06T14:30:00
-- Date only
2024-10-06
-- Time only
14:30:00
Datetime Handling
When querying datetime values on JSON fields using JSON query syntax, always set is_datetime: true
to ensure the query is parsed as a date.
SELECT id FROM mock_items WHERE mock_items @@@ '{
"range": {
"field": "metadata.attributes.tstz",
"lower_bound": {"included": "2023-05-01T08:12:34Z"},
"upper_bound": null,
"is_datetime": true
}
}'::jsonb
ORDER BY id;