Commits on Aug 6, 2026

1. Add tsmatch JSONPath operator for granular Full Text Search

   This patch introduces the tsmatch boolean operator to the JSONPath engine.
    By integrating FTS natively into path expressions, this operator allows
    for high-precision filtering of nested JSONB structures—solving issues
    with structural ambiguity and query complexity.
    Currently, users must choose between two suboptimal paths for searching
    nested JSON:
    1. Imprecise Global Indexing
       jsonb_to_tsvector aggregates text into a flat vector. This ignores
       JSON boundaries, leading to false positives when the same key
       (e.g., "body") appears in different contexts (e.g., a "Product
       Description" vs. a "Customer Review").
    2. Complex SQL Workarounds
       Achieving 100% precision requires "exploding" the document via
       jsonb_array_elements and LATERAL joins. This leads to verbose SQL
       and high memory overhead from generating intermediate heap tuples.
    One of the most significant advantages of tsmatch is its ability to
    participate in multi-condition predicates within the same JSON object—
    something jsonb_to_tsvector cannot do.
      SELECT jsonb_path_query(doc, '$.comments[*] ? (@.user == "Alice" && @.body tsmatch "performance")');
    In a flat vector, the association between "Alice" and "performance" is
    lost. tsmatch preserves this link by evaluating the FTS predicate
    in-place during path traversal.
    While the SQL/JSON standard (ISO/IEC 9075-2) does not explicitly define
    an FTS operator, tsmatch is architecturally modeled after the
    standard-defined like_regex.
    The operator supports optional configuration for both the dictionary and
    the query parser:
      @ tsmatch "query" [ tsconfig "regconfig" ] [ tsqparser "mode" ]
    Supported parser modes are:
     - "pl": plainto_tsquery (no operators required)
     - "ph": phraseto_tsquery
     - "w":  websearch_to_tsquery
     - Omitted: Defaults to to_tsquery (strict mode)
    The implementation relies on GIN path-matching for index pruning and
    heap re-checks for precision. Caching is scoped to the
    JsonPathExecContext, ensuring 'compile-once' efficiency for the
    tsquery and OID lookup per execution, respecting the stability
    requirements of prepared statements.

   

   authored and Commitfest Bot committed

   Aug 6, 2026
   Configuration menu

   Browse the repository at this point in the history

2. Configuration menu

   Browse the repository at this point in the history