Range - ParadeDB

Legacy Docs: This page describes our legacy API. It will be deprecated in a future version. Please use the v2 API where possible.

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, '()')
);

SELECT description, metadata
FROM mock_items
WHERE id @@@
'{
    "range": {
        "field": "metadata.attributes.quantity",
        "lower_bound": {"excluded": 2},
        "upper_bound": {"excluded": 5}
    }
}'::jsonb;

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;

​Basic Usage

​Datetime Values

​Datetime Handling

Basic Usage

Datetime Values

Datetime Handling