.. _constraints: constraints =========== The **constraints** parameter selects objects whose properties satisfy an expression. Expressions can compare values, call functions, and combine conditions. They apply to entities, images, connections, and other object types. A comparison tests one value against another. In ``["$age", ">=", 18]``, ``$age`` refers to the object's ``age`` property, ``>=`` means greater than or equal to, and ``18`` is the comparison value. The following query returns the names and ages of people aged 18 or over: .. code-block:: json [{"FindEntity": { "with_class": "Person", "constraints": ["$age", ">=", 18], "results": {"list": ["name", "age"]} }}] Without **constraints**, the command selects all objects eligible under its other parameters. The same expression syntax applies to connection filters and conditional adds through **if_not_found**. Adaptive full-text matching is restricted to read queries, as described under `Text scores`_. :doc:`../QueryingData` explains how filters fit into a complete query. :doc:`../examples/QuerySemantics` covers type, precision, and index behavior. .. _constraint-array-expressions: Array expressions ----------------- A comparison array has the form ``[left, operator, right]``. Its operands can be literal values, property references, or function calls. Boolean operators combine comparisons into larger expressions. Property references and literals ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A string beginning with ``$`` refers to a property: ``$name`` reads the ``name`` property. An extra dollar sign escapes a literal that begins with ``$``: ``$$USD`` represents the string ``$USD``. These rules also apply inside expression membership lists. Values stored inside a property remain literal data. The ``{"array": [...]}`` constructor evaluates its elements, including property references and functions. The ``{"json": [...]}`` constructor keeps its payload as literal JSON data. Raw arrays in operand positions represent boolean conditions rather than lists. Dates in expressions are created with ``to_date``, either from a literal such as ``{"to_date": "2026-09-13T00:00:00+00:00"}`` or from a property such as ``{"to_date": "$created_text"}``. The usual ``$`` reference and ``$$`` escaping rules apply to its argument, including when the date appears in a list. Property writes use ``_date`` to store datetimes. Comparisons and missing values ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The comparison operators are ``==``, ``!=``, ``<``, ``<=``, ``>``, ``>=``, ``in``, and ``not_in``. Both operands can reference properties or call scalar functions. For membership, the right operand can be a list constructor, a list property, or an ``at`` call that returns a list. A null candidate matches a missing property. Missing values compare equal to null, and two missing fields compare equal to each other. Inequality with a non-null scalar value can match a missing field. Equality and inequality require matching non-null scalar types: number, string, boolean, or date. For example, comparing the number ``1`` with the string ``"1"`` raises an error. Ordered comparisons require matching number, string, or date types. Booleans, lists, and objects are unsupported, even when the other operand is null. With otherwise valid operands, ordering against null returns false. JSON objects and blobs are unsupported scalar inputs and raise an error. ``is_missing`` accepts any operand and reports whether it evaluates to null or a missing value. ``["$name", "is_missing"]`` checks a property; ``[{"lower": "$name"}, "is_missing"]`` checks a computed result. ``is_finite`` returns true only for a finite numeric value. Strings, booleans, dates, null, and missing values return false. Both operators return false for lists and objects. Failed computations raise an error even inside ``is_missing`` or ``is_finite``. Scalar string equality, inequality, and membership use exact binary equality: case, accents, and different Unicode spellings remain distinct. String ordering uses the configured locale collator, with original bytes breaking collation ties. Applying ``casefold`` to both operands provides full Unicode caseless equality, without normalizing composed and decomposed spellings. List equality and inequality compare the complete contents recursively, including element order and length. Comparing a list with a scalar raises an error, whether detected during parsing or evaluation. Membership candidates and nested lists can contain different value types. In these contexts, values of different types do not match each other and do not raise a type error. Combining conditions ~~~~~~~~~~~~~~~~~~~~~~~ ``all`` matches when every child expression is true; ``any`` matches when at least one is true. Both take one or more child expressions directly. ``not`` negates exactly one expression. This filter selects active objects whose usage is below their limit and whose name does not begin with ``temp-``. A missing name also satisfies the negated condition: .. TESTED_EXAMPLE: constraints_7 .. code-block:: json ["all", ["$status", "==", "active"], ["$usage", "<", "$limit"], ["not", ["$name", "regex", "^temp-"]] ] List membership in array expressions ------------------------------------ When the left operand of ``in`` is a list, every left element must occur in the right list. ``not_in`` is the complement of this test. Ordering and duplicates do not affect this outer membership test; an empty left list is included in any right list, including an empty one. .. TESTED_EXAMPLE: constraints_12 .. code-block:: python ["$colors", "in", {"array": ["red", "blue"]}] ["red", "in", "$colors"] ["$colors", "not_in", "$allowed_colors"] List constructors ~~~~~~~~~~~~~~~~~~~~ A list constructor resolves property references recursively and preserves nesting. ``{"array": ["$colors"]}`` contains one element, the value of ``colors``; it does not copy the elements of ``colors`` into the surrounding list. Nested lists compare by their complete contents, including element order: .. TESTED_EXAMPLE: constraints_13 .. code-block:: python # If colors is ["red", "blue"]: ["$colors", "in", {"array": ["red", "blue"]}] # true ["$colors", "in", {"array": [{"array": ["red", "blue"]}]}] # false [{"array": ["$colors"]}, "in", {"array": [{"array": ["red", "blue"]}]}] # true [{"array": ["$colors"]}, "in", {"array": [{"array": ["blue", "red"]}]}] # false ["$colors", "==", {"array": ["red", "blue"]}] # true ["$colors", "==", {"array": ["blue", "red"]}] # false Expression list elements can also be null, nested lists, or objects. Objects compare by their complete contents. Numeric elements compare by value without rounding large integers to match a different floating-point value. A missing or non-list right operand raises an error for both ``in`` and ``not_in``. Invalid elements, including failed computations inside nested lists, also raise an error even if another candidate would match. Boolean ``not`` propagates these errors. Stored arrays and JSON ~~~~~~~~~~~~~~~~~~~~~~~~~ Stored Array properties have a narrower element set: finite numbers, strings, booleans, datetimes, and nulls. Property writes use raw arrays, with single-key ``_date`` string wrappers for datetime elements. These arrays may mix the supported element types and preserve order and duplicates, but reject nested arrays and objects. Query list constructors do not extend this storage format. An ``_json`` property stores arbitrary JSON. Its payload may be any JSON type, including an array with nulls, nested arrays, and objects: .. code-block:: json {"payload": {"_json": [null, [1, 2], {"name": "red"}]}} The property is stored as ``Json``, even when its payload is a scalar or array. An Array index covers native Array properties, not ``_json`` payloads. In expressions, JSON properties expose their payload's type and contents; a JSON null behaves as null for ``is_missing`` and ``coalesce``. See :doc:`properties` for storage syntax. A :doc:`../commands/CreateIndex` definition with ``kind: "array"`` can accelerate membership between a stored Array property and constant values or lists. Membership follows the same rules with or without an index. Expression elements and literal JSON ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Conditions keep their boolean meaning inside list constructors and membership operands. The ``array`` wrapper identifies the list; conditions inside it still evaluate to boolean values: .. TESTED_EXAMPLE: constraints_14 .. code-block:: python [["$usage", "<", "$limit"], "in", {"array": [True]}] [True, "in", {"array": [["$usage", "<", "$limit"]]}] Objects in expression positions must be a recognized function, ``array`` constructor, or ``json`` literal with exactly one key. Bare objects, including ``{}`` and objects with unknown or multiple keys, raise an error. Function objects inside ``array`` keep their expression meaning. A ``{"json": }`` expression contains literal JSON of any type. Its entire payload is data: strings beginning with ``$`` are not references, raw arrays are not conditions, and keys such as ``date``, ``_date``, ``lower``, ``array``, and ``json`` have no expression meaning. No expression escaping is needed inside this payload. For example: .. code-block:: text [{"json": "$name"}, "==", "$$name"] [{"json": [1, "==", 1]}, "==", {"array": [1, "==", 1]}] [{"array": [{"json": {"lower": "$name"}}]}, "in", {"json": [{"lower": "$name"}]}] [{"count": {"json": [null, [1, 2], {"key": 3}]}}, "==", 3] A ``json`` literal has its payload's expression type: strings work with string functions, arrays work with ``count``, ``merge``, ``at``, and membership, and objects remain available inside lists and through ``coalesce``. Direct object equality or membership operands raise an error. Objects wrapped in lists can be compared by their contents. Literal JSON null follows the usual null rules. Integers remain exact as data in lists and objects within the JSON parser's signed or unsigned 64-bit integer range; larger tokens may already have rounded to binary64. Consuming a JSON integer as a scalar number requires exact binary64 conversion and raises an error if precision would be lost. The distinction between the two constructors is evaluation: ``array`` evaluates references and functions, while ``json`` preserves its whole payload as data. The query wrapper is ``json``; property writes use ``_json``. Functions --------- Function calls use a single-key object. Unary functions take one argument; functions with multiple arguments take an array of arguments: .. TESTED_EXAMPLE: constraints_8 .. code-block:: python [{"length": {"trim": "$name"}}, ">", 3] [{"coalesce": ["$usage", 0]}, "<", "$limit"] ["$name", "contains", "report"] Boolean predicates such as ``contains``, ``starts_with``, ``ends_with``, and ``is_finite`` use operator syntax. Functions are grouped by the values they process or produce: * **Strings:** ``length``, ``lower``, ``upper``, ``casefold``, ``trim``, ``ltrim``, ``rtrim``, and ``concat``. * **Arrays:** ``count``, ``merge``, and ``at``. * **Numbers:** ``abs``, ``ceil``, ``floor``, ``add``, ``subtract``, ``multiply``, and ``divide``. * **Type conversion:** ``to_number``, ``to_string``, ``to_bool``, and ``to_date``. * **Explicit scalar conversion:** ``str_to_number``, ``number_to_str``, ``str_to_bool``, ``bool_to_str``, ``bool_to_number``, ``number_to_bool``, ``str_to_date``, and ``date_to_str``. * **Type checks:** ``is_number``, ``is_bool``, ``is_str``, and ``is_date``. * **Conditional and fallback values:** ``if`` and ``coalesce``. * **Text scores:** ``similarity``, ``isimilarity``, and ``fulltext``. String functions operate on UTF-8 text. Literal text comparisons are case-sensitive unless a case-insensitive operator is specified. ``concat``, ``merge``, and ``coalesce`` accept two to 4096 arguments, subject to the overall expression-size limit. ``add``, ``subtract``, ``multiply``, ``divide``, ``at``, ``similarity``, and ``isimilarity`` take two arguments, as do the ``contains``, ``starts_with``, and ``ends_with`` predicates. ``fulltext`` takes two to four arguments, as described in :ref:`text search expressions `. ``if`` takes exactly three arguments. The remaining functions take one argument. ``coalesce`` returns the first non-null value, including arrays and JSON objects. Empty arrays and objects are non-null. It preserves the selected value's type and contents; a consuming function or comparison still enforces its own type rules. For example, ``{"coalesce": ["$colors", {"array": []}]}`` returns an empty array when ``$colors`` is missing. A present non-array value is returned unchanged, so passing that result to ``count`` or ``merge`` raises a type error. Each function defines the types accepted at each argument position. Functions with a variable number of arguments repeat their argument-type rule. Functions that accept booleans, including ``coalesce``, ``to_number``, ``to_string``, and ``to_bool``, can take a condition array as an argument. These conditions follow the same comparison, missing-value, and short-circuit rules as filters. Conversion to a string or number requires an explicit function call: .. TESTED_EXAMPLE: constraints_9 .. code-block:: python [{"to_number": ["$usage", "<", "$limit"]}, "==", 1] [{"concat": ["eligible=", {"to_string": ["$usage", "<", "$limit"]}]}, "==", "eligible=true"] [{"coalesce": [["$usage", "is_missing"], false]}, "==", true] String and array functions -------------------------- ``trim``, ``ltrim``, and ``rtrim`` remove ordinary U+0020 spaces from both ends, the left end, or the right end respectively. Tabs, newlines, and nonbreaking spaces remain. ``length`` accepts strings and counts Unicode code points, rather than bytes or grapheme clusters. For example, composed ``é`` has length 1, while ``e`` followed by U+0301 COMBINING ACUTE ACCENT has length 2. ``count`` accepts arrays and returns their number of immediate elements; empty arrays have count zero, and a nested array counts as one element. For example, ``{"count": {"array": [1, {"array": [2, 3]}]}}`` returns 2. ``count`` rejects strings and other non-array values. Null and missing inputs propagate null. ``concat`` joins strings in argument order. It does not automatically convert numbers, booleans, dates, or arrays. The ``to_string`` function converts supported scalar values explicitly. Invalid argument types raise an error. Each evaluated string must be valid UTF-8, and the combined result cannot exceed 16 MiB. ``merge`` joins arrays in argument order, preserving duplicates, element types, and nested arrays as individual elements. It accepts stored arrays and expression lists, including arrays returned by other functions. For example, ``{"merge": [{"array": [1, 2]}, {"array": [2, {"array": [3]}]}]}`` returns ``[1, 2, 2, [3]]``. An empty array contributes no elements. Non-array arguments raise an error. Both ``concat`` and ``merge`` return null when an evaluated argument is null or missing. Evaluation follows the function short-circuit rules described below; failed computations are errors, not empty strings or arrays. Selecting array elements ------------------------ ``{"at": [array, index]}`` returns the element at a zero-based index. The array argument can be a stored array property, an expression list, another ``at`` call, or a computed array where field access is permitted. The index can be a number, a property reference, or a numeric expression. The selected element keeps its type, including nested arrays and JSON objects: .. TESTED_EXAMPLE: constraints_15 .. code-block:: python [{"at": ["$colors", 0]}, "==", "red"] [{"at": ["$scores", "$position"]}, ">", 10] [{"at": ["$scores", {"subtract": ["$position", 1]}]}, ">", 10] [{"coalesce": [{"at": ["$colors", 99]}, "unknown"]}, "==", "unknown"] An out-of-bounds index returns null, including a large finite whole-number index such as ``1e20``. A missing array or index also returns null, allowing ``coalesce`` to supply a fallback. Indexes must be finite, nonnegative whole numbers. Negative indexes do not count from the end; they raise an error, as do fractional indexes, indexes of the wrong type, and non-array inputs. ``coalesce`` does not catch these errors. Statically known type mismatches and calls with anything other than two arguments are rejected when parsing the query. Every element of an expression list is evaluated before selection, so a failing computation in an unselected element also raises an error. An explicit list argument takes the form ``{"array": [...]}``, with property references and ``$$`` escaping supported inside the list. ``at`` preserves nested boundaries: selecting the first element of ``{"array": ["$colors"]}`` returns the entire ``colors`` list. A selected list can be compared with another list, or used with membership or another ``at``: .. TESTED_EXAMPLE: constraints_16 .. code-block:: python # If colors is ["red", "blue"]: [{"at": [{"array": ["$colors"]}, 0]}, "in", {"array": ["red", "blue"]}] ["red", "in", {"at": [{"array": ["$colors"]}, 0]}] [{"at": [{"at": [{"array": ["$colors"]}, 0]}, 1]}, "==", "blue"] [{"at": [{"array": [1, "==", 1]}, 1]}, "==", "=="] Array indexes configured for the selected position can accelerate scalar ``==``, ``!=``, range comparisons, ``is_missing``, ``is_finite``, and membership in a constant list. Positions and comparison values can be field-free expressions. Indexes do not suppress type errors for incompatible values. A null element and an out-of-range position both return null from ``at``. String ``==``, ``!=``, and membership use exact binary equality. Ordered comparisons use the configured locale collation, with original bytes breaking ties. Selected lists retain structural equality and membership semantics. Selected objects retain their contents when passed through ``coalesce`` or wrapped in an expression list. Comparing a selected list with a scalar raises an error. Using a selected object as a scalar or passing a list to a function that does not accept lists also raises an error. Type checks and conditional values ---------------------------------- ``is_number``, ``is_bool``, ``is_str``, and ``is_date`` return a boolean indicating whether their argument has that type, without converting it. Numbers and booleans are distinct: ``{"is_number": true}`` is false, as is ``{"is_bool": 1}``. Null, missing properties, arrays, and objects return false for each of these checks. Use the ``is_missing`` operator to check for null or missing values. A type check does not catch errors in its argument. ``{"if": [condition, when_true, when_false]}`` returns the second argument when the condition is true and the third when it is false. The condition can be a boolean value, property reference, function, or condition array. Other non-null types raise an error; there is no implicit truthiness conversion. A null or missing condition returns null without evaluating either branch. The selected branch retains its value and type. Only the selected branch is evaluated, including when an unselected branch contains an invalid constant conversion or division by zero. Both branches must still have valid syntax and statically compatible argument types. For example:: {"if": [{"is_number": "$value"}, {"number_to_str": "$value"}, "not a number"]} {"if": [["$divisor", "!=", 0], {"divide": ["$value", "$divisor"]}, null]} {"if": [true, 1, {"str_to_number": "not a number"}]} Type conversion --------------- The unary converters accept the following types: * ``to_number`` maps ``false`` to 0 and ``true`` to 1, preserves numbers, and parses decimal strings with an optional leading minus and decimal exponent. The entire string must parse; whitespace, a leading plus, trailing characters, nonfinite values, and values outside the representable range raise an error. Plain integer strings (an optional minus followed by digits) must be exactly representable as a binary64 number. For example, ``"9007199254740993"`` raises an error while ``"9007199254740994"`` succeeds. This exact-conversion rule permits values beyond ±2^53, including integer strings too large for a 64-bit integer type when binary64 represents them exactly. Leading zeros do not change this rule. Strings containing a decimal point or exponent retain ordinary binary64 conversion, which may round. * ``to_string`` preserves strings and formats finite numbers using a locale-independent shortest round-trip representation. It may use scientific notation: for example, ``100000`` can become ``"1e+05"``. It does not guarantee fixed decimal places or offer display-precision options. It maps booleans to ``"true"`` or ``"false"`` and formats dates with the same ISO representation and stored timezone offset used in property results. * ``to_bool`` preserves booleans, maps zero to false and other finite numbers to true, and accepts exactly the case-sensitive strings ``"true"`` and ``"false"``. * ``to_date`` parses a string with the same date parser used by property writes and preserves an existing date value. It accepts both literal strings and computed strings or property references. Explicit source-type converters use the same conversion rules, but accept only the type named before ``_to_``: .. list-table:: :header-rows: 1 :widths: 40 30 30 * - Function - Input type - Result type * - ``str_to_number`` - String - Number * - ``number_to_str`` - Number - String * - ``str_to_bool`` - String - Boolean * - ``bool_to_str`` - Boolean - String * - ``bool_to_number`` - Boolean - Number * - ``number_to_bool`` - Number - Boolean * - ``str_to_date`` - String - Date * - ``date_to_str`` - Date - String All eight explicit converters return null for null or missing inputs. Other input types and invalid conversions raise an error. For example, ``{"str_to_number": "42"}`` returns 42, while ``{"str_to_number": 42}`` raises a type error. Numeric precision -------------------- Ordinary signed and unsigned integer literals in constraints must be within the inclusive range ``[-9007199254740992, 9007199254740992]`` (±2^53). Decimal and exponent numeric tokens and arithmetic use binary64 and may round. Numeric tokens too large for the JSON parser's integer representation can also enter its floating-point path, so they are not guaranteed to be rejected by the integer range check. Native Array elements, ``json`` payloads, and ``to_number`` integer strings have distinct precision rules, described in `List constructors`_, `Expression elements and literal JSON`_, `Type conversion`_, and :doc:`properties`. Null values and errors ---------------------- All functions raise an error for unsupported input types or failed computations, including failed conversions, division by zero, and nonfinite arithmetic operands or results. ``to_date``, ``to_string``, and ``casefold`` also reject null or missing inputs. The explicit source-type converters, ``to_number``, ``to_bool``, and ordinary string, numeric, and array functions propagate null or missing values. Type checks return false for null or missing values. ``if`` returns null for a null or missing condition; a null value in an unselected branch has no effect. ``coalesce`` skips null or missing values and returns the first non-null value. ``coalesce`` does not catch computation errors. No implicit scalar conversions or date arithmetic are performed. Evaluation short-circuits: ``all``, ``any``, and ``coalesce`` can skip later operands, and a null argument can stop evaluation of a null-propagating function. Expressions that do not reference properties are evaluated before the query runs, so an error in a constant expression fails the query even when its branch would otherwise be skipped. ``if`` is an exception: computation errors in an unselected branch are deferred and do not fail the query. For example, if ``$missing`` is absent and ``$bad`` contains ``"bad"``, ``{"add": ["$missing", {"to_number": "$bad"}]}`` returns null; reversing the arguments raises a conversion error. Replacing ``"$bad"`` with the constant string ``"bad"`` raises an error before the query runs in either order. Likewise, ``{"coalesce": [5, {"divide": [1, 0]}]}`` raises an error before the query runs. Pattern matching ---------------- ``like``, ``regex``, ``contains``, ``starts_with``, ``ends_with``, and their case-insensitive variants require string operands: a literal, a property reference, or a function that returns a string. A literal number or boolean, or a numeric function such as ``length``, is rejected when the query is parsed. ``regex`` and ``iregex`` use RE2 search semantics. ``regex`` is case-sensitive by default; ``iregex`` enables RE2 case-insensitive matching, equivalent to a leading ``(?i)`` flag. RE2 inline flags can override this default; lookaround and backreferences are not supported. ``iregex`` uses RE2's case handling rather than the full Unicode case folding used by case-insensitive literal operators. ``like`` matches the whole string: ``%`` matches zero or more code points and ``_`` matches one. The only LIKE escapes are a backslash followed by ``%``, ``_``, or another backslash. An unsupported escape or trailing backslash raises ``Invalid LIKE escape``. These rules also apply to ``ilike``. Pattern operands can be literals, fields, or functions: .. TESTED_EXAMPLE: constraints_10 .. code-block:: python ["$name", "regex", "$pattern"] ["$name", "iregex", "report"] ["$name", "like", {"concat": ["$prefix", "%"]}] JSON decoding happens before LIKE parsing. The JSON text must therefore escape each backslash needed by the LIKE pattern: .. list-table:: Literal characters in LIKE patterns :header-rows: 1 * - Literal to match - JSON pattern string - Pattern after JSON decoding * - ``%`` - ``"\\%"`` - ``\%`` * - ``_`` - ``"\\_"`` - ``\_`` * - One backslash - ``"\\\\"`` - Two backslashes: ``\\`` Each of these JSON expressions matches: .. code-block:: text ["100%", "like", "100\\%"] ["file_name", "like", "file\\_name"] ["a\\b", "like", "a\\\\b"] For example, the valid JSON string ``"bad\\q"`` decodes to ``bad\q`` and then fails LIKE parsing because ``\q`` is not a supported escape. ``ilike``, ``icontains``, ``istarts_with``, and ``iends_with`` are case-insensitive versions of ``like``, ``contains``, ``starts_with``, and ``ends_with``. Both operands are compared after Unicode full case folding, so ``"Straße"`` matches ``"STRASSE"`` and ``"Café"`` matches ``"CAFÉ"``. In ``ilike``, ``%`` and ``_`` apply to the folded text: ``"ß" like "_"`` matches, but ``"ß" ilike "_"`` does not because folding produces ``"ss"``. The pattern ``"__"`` matches that folded value. The right operand may be a literal, a property reference, or a function; a null operand never matches. The unary ``casefold`` function returns the same full Unicode folded text, so it can be used explicitly in expressions: .. code-block:: text [{"casefold": "$name"}, "==", {"casefold": "$expected_name"}] [{"casefold": "Straße"}, "==", "strasse"] ``casefold`` requires a string. Null or missing input, other input types, invalid UTF-8, and input or output longer than 16 MiB raise an error. Folding can expand the result, and folding an already folded string leaves it unchanged. ``lower`` and ``upper`` use root-locale case conversion. For ``"Straße"``, ``lower`` returns ``"straße"``, ``upper`` returns ``"STRASSE"``, and ``casefold`` returns ``"strasse"``. Full folding also maps both Greek ``σ`` and final ``ς`` to ``σ``. Case conversion and case folding do not perform Unicode normalization: composed ``é`` and decomposed ``e\u0301`` remain distinct strings. ``iregex`` uses RE2 case handling, independently of these functions and the full-folding operators. This query uses ``ilike`` to match names beginning with ``CREATURE1``, regardless of case: .. TESTED_EXAMPLE: constraints_11 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": ["$name", "ilike", "CREATURE1%"], "sort": "name", "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_11_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature1" }, { "_class": "living", "name": "creature10" }], "returned": 2, "status": 0 } }] Text scores ----------- ``similarity`` and ``isimilarity`` take two strings and return a score from 0 to 1. ``similarity`` compares the original strings. ``isimilarity`` applies Unicode full case folding to both strings first, so ``Straße`` and ``STRASSE`` have score 1. Neither function normalizes Unicode spellings. Null or missing arguments propagate null under the function evaluation rules above; other non-string inputs and invalid UTF-8 raise an error. The score is normalized optimal string alignment (OSA): one minus the edit distance divided by the longer code-point length. Insertions, deletions, substitutions, and adjacent transpositions cost one. ``cat``/``cut`` and ``cat``/``act`` score approximately 2/3; ``aaa``/``aaaa`` scores 3/4. Two empty strings score 1, and exactly one empty string scores 0. Case-insensitive OSA measures both distance and lengths after folding. See :ref:`text-search` for the matching rules, index setup, and ranked query examples. ``fulltext`` returns a BM25 term score from a ready full-text index. Its optional third argument is ``"all"`` (the default), requiring every distinct query term, or ``"any"``, requiring at least one. Text scoring functions work inside ordinary comparisons and ``all``/``any``/``not`` conditions. The first ``fulltext`` argument must be a direct native string property reference and the second a literal query string. Its optional fourth argument is a literal integer ``max_edits`` from 0 through 256; supplying it also requires the third ``"all"`` or ``"any"`` argument. Positive values enable adaptive matching in read queries only, and are rejected in conditional adds and update/delete constraints. An omitted fourth argument, or a value of 0, selects exact-token matching. See :ref:`text search expressions ` for complete semantics and examples that explicitly sort by expressions and return multiple named scores. .. code-block:: text [{"similarity": ["$name", "report"]}, ">=", 0.5] [{"isimilarity": ["Straße", "STRASSE"]}, "==", 1] [0.5, "<=", {"isimilarity": ["REPORT", "$name"]}] Indexes ------- Most constraint expressions work without indexes. The ``fulltext`` function requires a full-text index on the class and property being searched. :doc:`../commands/CreateIndex` defines the indexes available for each filter. .. list-table:: Indexes for common filters :header-rows: 1 * - Filter - Index kind * - Property equality or range comparison with a constant scalar - ``ordered``; use case_sensitive or dual_case for exact string comparisons. One definition covers the supported native scalar types. Adjacent compatible native numeric bounds can share one interval lookup. * - A scalar property ``in`` a small homogeneous constant list - ``ordered`` for supported native numbers, strings, booleans, or dates. Candidates retain the expression's membership comparison. * - ``contains``, ``starts_with``, ``ends_with``, and their case-insensitive variants - ``trigram`` with the matching case mode; the search string must contain at least three code points after any case folding. * - ``similarity`` or ``isimilarity`` with a positive-score condition - ``similarity`` with the matching case mode. * - ``fulltext`` - ``fulltext`` (required). * - ``in`` or ``not_in`` between a native Array property and constant values or lists - ``array``. * - Constant-position ``at`` comparisons, membership, ``is_missing``, or ``is_finite`` - ``array`` with the position configured. Constant expressions can supply the position or comparison value. * - ``count`` of a native array compared with a constant, or whole-array ``==`` / ``!=`` - ``array``, including definitions without positions. Structural equality retains verification. * - Property presence or type checks - ``type``. Array presence can substitute when the requested stored domain contains only arrays. A position field can use a configured positional index when an immediately preceding equality fixes it: .. code-block:: json ["all", ["$slot", "==", 0], [{"at": ["$values", "$slot"]}, "==", 7] ] The equality remains part of evaluation. Fractional or incompatible values are not rounded into positions, and moving the equality after the ``at`` condition does not grant the same access. This optimization requires a safe stored numeric position field and does not propagate bindings through arbitrary expressions. Ordered and trigram indexes do not require a separate ``type`` index. A type index can help with property-presence and type queries. Similarity, full-text, and array indexes also work without a separate type index. An index does not change type checking or query errors. JSON properties are compared using their decoded values; for example, JSON null satisfies ``is_missing``. Computed properties cannot be indexed through ``CreateIndex``. For similarity filters, one argument must be a stored property reference and the other a literal string to use the index. ``similarity`` uses a ``case_sensitive`` or ``dual_case`` index; ``isimilarity`` uses ``case_insensitive`` or ``dual_case``. Positive-score conditions include ``score > t`` for ``t >= 0`` and ``score >= t`` or ``score == t`` for ``t > 0``. Either comparison orientation and either argument order are supported: .. code-block:: text [{"isimilarity": ["$name", "REPORT"]}, ">=", 0.5] [0.5, "<=", {"isimilarity": ["REPORT", "$name"]}] An available index is not necessarily used for every query. Selection depends on the expression, property types, and number of candidate records. Indexes do not change comparison results, short-circuit behavior, or type errors. A small **limit** bounds returned records, not the amount of filtering work. Explicit ``count`` covers every match before pagination. Sums, averages, minima, and maxima apply to the selected page. Wrapping a property in a function does not create an index on the function's result. For example, an ordered index on a property does not index its absolute value or an arbitrary arithmetic expression. See the index eligibility table above and :doc:`../commands/CreateIndex` when choosing indexes for your queries. Complete query examples ----------------------- These examples query a populated ``living`` class. The responses illustrate the matches for the sample data; an empty class returns no matches. :doc:`../QueryingData` includes an example that creates its own data. The first query combines comparisons with ``all``. Two comparisons on ``age`` define an inclusive range from 20 to 90. The other conditions require a score of at most 75 and an area of 51: .. TESTED_EXAMPLE: constraints_1 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "all", ["$age", ">=", 20], ["$age", "<=", 90], ["$score", "<=", 75], ["$area", "==", 51] ], "results": { "list": ["age"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_1_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "age": 20 }, { "_class": "living", "age": 40 }, { "_class": "living", "age": 60 }, { "_class": "living", "age": 80 }], "returned": 4, "status": 0 } }] Membership filters ~~~~~~~~~~~~~~~~~~ For a scalar property, ``in`` matches any candidate in the list. This query adds a name filter to the age, score, and area conditions: the name must be ``creature4`` or ``creature6``. Candidate order does not determine result order; the **sort** parameter controls ordering. .. TESTED_EXAMPLE: constraints_2 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "all", ["$age", ">=", 20], ["$age", "<=", 90], ["$score", "<=", 75], ["$area", "==", 51], [ "$name", "in", { "array": ["creature4", "creature6"] } ] ], "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_2_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature4" }, { "_class": "living", "name": "creature6" }], "returned": 2, "status": 0 } }] Alternative conditions ~~~~~~~~~~~~~~~~~~~~~~ An ``any`` expression matches at least one alternative. Here, the nested ``all`` groups both age bounds into a single alternative; the other branches match by score, area, or name: .. TESTED_EXAMPLE: constraints_3 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "any", [ "all", ["$age", ">=", 20], ["$age", "<=", 90] ], ["$score", "<=", 75], ["$area", "==", 51], [ "$name", "in", { "array": ["creature3", "creature5"] } ] ], "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_3_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature1" }, { "_class": "living", "name": "creature2" }, { "_class": "living", "name": "creature3" }, { "_class": "living", "name": "creature4" }, { "_class": "living", "name": "creature5" }, { "_class": "living", "name": "creature6" }, { "_class": "living", "name": "creature7" }, { "_class": "living", "name": "creature8" }, { "_class": "living", "name": "creature9" }, { "_class": "living", "name": "creature10" }], "returned": 10, "status": 0 } }] With ``all``, the name and every other condition must match: .. TESTED_EXAMPLE: constraints_4 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "all", ["$age", ">=", 20], ["$age", "<=", 90], ["$score", "<=", 75], ["$area", "==", 51], [ "$name", "in", { "array": ["creature2"] } ] ], "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_4_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature2" }], "returned": 1, "status": 0 } }] Datetime comparisons ~~~~~~~~~~~~~~~~~~~~ The ``to_date`` function converts a timestamp string into a datetime operand. This query compares it with the stored ``dob`` property: .. TESTED_EXAMPLE: constraints_5 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "$dob", "==", { "to_date": "2018-02-27T13:45:12-08:00" } ], "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_5_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature1" }], "returned": 1, "status": 0 } }] Combined conditions ~~~~~~~~~~~~~~~~~~~~~~ A single filter can combine numeric ranges, membership, and datetime comparisons. Here, a match requires an age from 20 to 90, one of three names, and a ``dob`` later than the specified timestamp: .. TESTED_EXAMPLE: constraints_6 .. code-block:: json [{ "FindEntity": { "with_class": "living", "constraints": [ "all", ["$age", ">=", 20], ["$age", "<=", 90], [ "$name", "in", { "array": ["creature1", "creature2", "creature3"] } ], [ "$dob", ">", { "to_date": "2018-02-27T13:45:12-08:00" } ] ], "results": { "list": ["name"] } } }] Successful response: .. TESTED_EXAMPLE: constraints_6_response .. code-block:: json [{ "FindEntity": { "entities": [{ "_class": "living", "name": "creature2" }, { "_class": "living", "name": "creature3" }], "returned": 2, "status": 0 } }] .. _constraint-resource-limits: Resource limits --------------- Expressions that exceed resource limits fail with a query error. String and pattern sizes below count UTF-8 bytes, not characters; KiB and MiB use powers of 1024. .. list-table:: Constraint resource limits :header-rows: 1 * - Resource - Limit and scope * - One parsed constraint expression - 4096 syntax nodes, including conditions, function and constructor operands, and individual computed-list entries. This is not a count of comparisons. * - Entire request JSON - Nesting depth 25 and at most 10000 members per object, including data inside ``json`` and ``_json`` payloads. * - ``regex``, ``iregex``, ``like``, and ``ilike`` pattern sources - 64 KiB after JSON decoding. For ``ilike``, both the original and folded source must fit. * - ``lower``, ``upper``, and ``casefold`` - 16 MiB for each input and output string. * - ``trim``, ``ltrim``, and ``rtrim`` - 16 MiB for the result after trimming. A larger input can succeed if the result fits. * - ``concat`` - 16 MiB for the accumulated result. * - Full-folding string operators - ``ilike`` subjects and both operands of ``icontains``, ``istarts_with``, and ``iends_with`` have a 16 MiB limit before folding. ``ilike`` patterns have the smaller source limit above. * - ``similarity`` and ``isimilarity`` - 16 MiB for each operand before and after any case folding. * - ``fulltext`` - 64 KiB of query text and at most 1024 distinct indexed terms. See :ref:`text search expressions ` for text input limits. * - UTF-8 processing - Other UTF-8 strings are limited to 2147483647 bytes. ``iregex`` subjects do not have the full-folding operators' 16 MiB limit. An opaque ``{"json": ...}`` literal counts as one expression operand; its payload contents are not separate syntax nodes. The request JSON limits still apply to those contents. Argument-count and expression-size limits both apply. Complex expressions and patterns are also subject to memory and computation limits.