Overview

Use . to search over text values nested inside JSON. For instance, the following query searches over a field with values like {"metadata": {"color": "white"}}.

The following query builder functions support JSON fields: term, fuzzy term, phrase, fuzzy phrase, phrase prefix, and range.

​

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;