Re: Patch: Add tsmatch JSONPath operator for granular Full Text Search

Here's a v5 which is v4 + pgindent

On Fri, Apr 3, 2026 at 12:56 PM Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
wrote:

>
>
>
> On Mon, Mar 2, 2026 at 5:44 AM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>
>>
>>
>> > On Feb 27, 2026, at 13:59, Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
>> wrote:
>> >
>> >
>> >
>> > On Thu, Feb 26, 2026 at 8:48 AM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>> >
>> >
>> > > On Feb 1, 2026, at 19:02, Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
>> wrote:
>> > >
>> > >
>> > >
>> > >
>> > > On Mon, Jan 26, 2026 at 7:22 PM Florents Tselai <
>> florents(dot)tselai(at)gmail(dot)com> wrote:
>> > > Hi,
>> > >
>> > > in real-life I work a lot with json & fts search, here's a feature
>> I've always wished I had,
>> > > but never tackle it. Until yesterday that is.
>> > >
>> > > SELECT jsonb_path_query(doc, '$.comments[*] ? (@.user == "Alice" &&
>> @.body tsmatch "performance")');
>> > >
>> > > This patch introduces a 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 FTS-ing
>> nested JSON:
>> > > - 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").
>> > >
>> > > - Complex SQL Workarounds
>> > > Achieving 100% precision requires unnesting 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 implementation follows the like_regex precedent:
>> > > it is a non-indexable predicate that relies on GIN path-matching for
>> pruning and heap re-checks for precision.
>> > > Caching is scoped to the JsonPathExecContext,
>> > > ensuring 'compile-once' efficiency per execution without violating
>> the stability requirements of prepared statements.
>> > >
>> > > This initial implementation uses plainto_tsquery.
>> > > However, the grammar is designed to support a "mode" flag (similar to
>> like_regex flags)
>> > > in future iterations to toggle between to_tsquery,
>> websearch_to_tsquery, and phraseto_tsquery.
>> > >
>> > > Here's a v2, that implements the tsqparser clause
>> > >
>> > > So this should now work too
>> > >
>> > > select jsonb_path_query_array('["fast car", "slow car", "fast and
>> furious"]', '$[*] ? (@ tsmatch "fast car" tsqparser "w")
>> <v2-0001-Add-tsmatch-JSONPath-operator-for-granular-Full-T.patch>
>> >
>> > Hi Florents,
>> >
>> > Grant pinged me about this. I can review it in coming days. Can you
>> please rebase it? I failed to apply to current master. Also, the CF
>> reported a failure test case, please take a look.
>> >
>> > Hi Evan,
>> > thanks for having a look. The conflict was due to the intro of
>> pg_fallthrough. Not related to this patch .
>> >
>> > I noticed the failure too, but I'm having a hard time reproducing it
>> tbh.
>> > This fails for Debian Trixie with Meson. The same with Autoconf
>> passes...
>> >
>> > https://github.com/Florents-Tselai/postgres/runs/65098077968
>> >
>> >
>> >
>> >
>> > <v3-0001-Add-tsmatch-JSONPath-operator-for-granular-Full-T.patch>
>>
>> I have reviewed v3 and traced a few test cases. Here comes my review
>> comments:
>>
>> 1
>> ```
>> + <replaceable>string</replaceable> <literal>tsmatch</literal>
>> <replaceable>string</replaceable>
>> + <optional> <literal>tsconfig</literal>
>> <replaceable>string</replaceable> </optional>
>> + <optional> <literal>tsqparser</literal>
>> <replaceable>string</replaceable> </optional>
>> ```
>>
>> For all “replaceable”, instead of “string”, would it be better to use
>> something more descriptive? For example:
>> ```
>> <replaceable>json_string</replaceable> <literal>tsmatch</literal>
>> <replaceable>query</replaceable>
>> <optional> <literal>tsconfig</literal>
>> <replaceable>config_name</replaceable> </optional>
>> <optional> <literal>tsqparser</literal>
>> <replaceable>parser_mode</replaceable> </optional>
>> ```
>>
>> 2 - jsonpath_gram.y
>> ```
>> +static bool makeItemTsMatch(JsonPathParseItem *doc,
>> + JsonPathString
>> *tsquery,
>> + JsonPathString
>> *tsconfig,
>> + JsonPathString
>> *tsquery_parser,
>> +
>> JsonPathParseItem ** result,
>> + struct Node
>> *escontext);
>> ```
>>
>> Format Nit: Looking at the existing code, the J in the second and
>> following lines, should be placed in the same column as the J in the first
>> line.
>>
>> 3 - jsonpath_gram.y
>> ```
>> + | expr TSMATCH_P STRING_P
>> + {
>> + JsonPathParseItem *jppitem;
>> + /* Pass NULL for tsconfig (3rd) and NULL for
>> tsquery_parser (4th) */
>> + if (! makeItemTsMatch($1, &$3, NULL, NULL, &jppitem,
>> escontext))
>> + YYABORT;
>> + $$ = jppitem;
>> + }
>> + | expr TSMATCH_P STRING_P TSCONFIG_P STRING_P
>> + {
>> + JsonPathParseItem *jppitem;
>> + /* Pass NULL for tsquery_parser (4th) */
>> + if (! makeItemTsMatch($1, &$3, &$5, NULL, &jppitem,
>> escontext))
>> + YYABORT;
>> + $$ = jppitem;
>> + }
>> + | expr TSMATCH_P STRING_P TSQUERYPARSER_P STRING_P
>> + {
>> + JsonPathParseItem *jppitem;
>> + /* Pass NULL for tsconfig (3rd) */
>> + if (! makeItemTsMatch($1, &$3, NULL, &$5, &jppitem,
>> escontext))
>> + YYABORT;
>> + $$ = jppitem;
>> + }
>> + | expr TSMATCH_P STRING_P TSCONFIG_P STRING_P TSQUERYPARSER_P
>> STRING_P
>> + {
>> + JsonPathParseItem *jppitem;
>> + if (! makeItemTsMatch($1, &$3, &$5, &$7, &jppitem,
>> escontext))
>> + YYABORT;
>> + $$ = jppitem;
>> + }
>> ```
>>
>> Feels a little redundant, repeatedly calls makeItemTsMatch. See the
>> attached diff for a simplification. But my version is a bit longer in terms
>> of number of lines. So, up to you.
>>
>> 4 - jsonpath_gram.y
>> ```
>> +static bool
>> +makeItemTsMatch(JsonPathParseItem *doc,
>> + JsonPathString *tsquery,
>> + JsonPathString *tsconfig,
>> + JsonPathString *tsquery_parser,
>> + JsonPathParseItem **result,
>> + struct Node *escontext)
>> ```
>>
>> makeItemTsMatch doesn’t need to return a bool. Actually, now it never
>> returns false, instead, it just ereport(ERROR).
>>
>> 5 - jsonpath.h
>> ```
>> + struct
>> + {
>> + int32 doc;
>> + char *tsquery;
>> + uint32 tsquerylen;
>> + int32 tsconfig;
>> + char *tsqparser;
>> + uint32 tsqparser_len;
>> + } tsmatch;
>>
>> + struct
>> + {
>> + JsonPathParseItem *doc;
>> + char *tsquery;
>> + uint32 tsquerylen;
>> + JsonPathParseItem *tsconfig;
>> + char *tsqparser;
>> + uint32 tsqparser_len;
>> + } tsmatch;
>> } value;
>> ```
>>
>> tsquerylen doesn’t have _ before len, and tsqparser_len, would it be
>> better to make naming conventions consistent in the same structure?
>>
>> 6 - jsonpath_exec.c
>> ```
>> #include "tsearch/ts_utils.h"
>> #include "tsearch/ts_cache.h"
>> #include "utils/regproc.h"
>> #include "catalog/namespace.h"
>>
>> static JsonPathBool
>> executeTsMatch(JsonPathItem *jsp, JsonbValue *str, JsonbValue *rarg,
>> void *param)
>> ```
>>
>> Why don’t put these includes to the header section together with other
>> includes?
>>
>> 7 - jsonpath_exec.c
>> ```
>> + else
>> + {
>> + /*
>> + * Fallback or Error for unknown flags
>> (should be caught by
>> + * parser)
>> + */
>> + ereport(ERROR,
>> +
>> (errcode(ERRCODE_SYNTAX_ERROR),
>> + errmsg("unrecognized
>> tsqparser flag")));
>> + }
>> ```
>>
>> This “else” should never be entered as the same check has been done by
>> makeItemTsMatch. So, maybe just use an Assert here, or pg_unreachable().
>>
>> 8 - jsonpath_exec.c
>> ```
>> + /* Setup Context (Run ONLY once per predicate) */
>> + if (!cxt->initialized)
>> ```
>>
>> While tracing this SQL:
>> ```
>> evantest=# SELECT '{"tags": ["running", "jogging"]}'::jsonb
>> evantest-# @@ '$.tags[*] ? (@ tsmatch "run" tsconfig "english")';
>> ?column?
>> ----------
>>
>> (1 row)
>> ```
>>
>> I noticed that, when process “jogging”, cxt->initialized is still false,
>> meaning that, the cxt is not reused across array items. Given the same
>> tsconfig should apply to all array items, I think cxt should be reused.
>>
>> 9 - jsonpath_exec.c
>> ```
>> + /* Select Parser and Compile Query */
>> + parser_mode = jsp->content.tsmatch.tsqparser;
>> + parser_len = jsp->content.tsmatch.tsqparser_len;
>> +
>> + if (parser_len > 0)
>> + {
>> + /* Dispatch based on flag */
>> + if (pg_strncasecmp(parser_mode, "pl", parser_len)
>> == 0)
>> ```
>>
>> Nit: parser_mode is only used inside if (parser_len > 0), it can be
>> defined inside the “if”.
>>
>> 10 - jsonpath_gram.y
>> ```
>> + ereport(ERROR,
>> + (errcode(ERRCODE_SYNTAX_ERROR),
>> + errmsg("invalid tsquery_parser
>> value: \"%s\"", tsquery_parser->val),
>> + errhint("Valid values are
>> \"pl\", \"ph\", and \"w\".")));
>> ```
>>
>> When tested a case with an invalid parser, I got:
>> ```
>> evantest=# SELECT '{"tags": ["running", "jogging"]}'::jsonb
>>
>> @? '$.tags[*] ? (@ tsmatch "run" tsconfig "english" tsqparser
>> "pss")';
>> ERROR: invalid tsquery_parser value: "pss @"
>> LINE 2: @? '$.tags[*] ? (@ tsmatch "run" tsconfig "english" tsqpar...
>> ^
>> HINT: Valid values are "pl", "ph", and "w".
>> ```
>>
>> You can see the it shows a bad looking invalid value. I think that’s
>> because tsquery_parser->val is not NULL terminated. I fixed this problem
>> with:
>> ```
>> errmsg("invalid tsquery_parser value: \"%.*s\"", (int)
>> tsquery_parser->len, tsquery_parser->val),
>> ```
>>
>> This change is also included in the attached diff file.
>>
>> 11 - jsonpath.c
>> ```
>> + if (printBracketes)
>> + appendStringInfoChar(buf, ')');
>> + break;
>> +
>> if (printBracketes)
>> appendStringInfoChar(buf, ')');
>> ```
>>
>> Duplicate code. Looks like a copy-pasto.
>>
>> 12 - jsonpath.c
>> ```
>> + /* Write the Main Query String */
>> + appendBinaryStringInfo(buf,
>> +
>> &item->value.tsmatch.tsquerylen,
>> +
>> sizeof(item->value.tsmatch.tsquerylen));
>> + appendBinaryStringInfo(buf,
>> +
>> item->value.tsmatch.tsquery,
>> +
>> item->value.tsmatch.tsquerylen);
>> + appendStringInfoChar(buf, '\0');
>> ```
>>
>> I don’t think we need to manually append ‘\0’ after
>> appendBinaryStringInfo. Looking at the header comment of
>> appendBinaryStringInfo, it says that a trailing null will be added.
>> ```
>> /*
>> * appendBinaryStringInfo
>> *
>> * Append arbitrary binary data to a StringInfo, allocating more space
>> * if necessary. Ensures that a trailing null byte is present.
>> */
>> void
>> appendBinaryStringInfo(StringInfo str, const void *data, int datalen)
>> ```
>>
>
> Here's a v4 which incorporates most of Evan's comments & feedback
> - shifted tsquery compilation logic to use persistent cache within
> JsonPathExecContext
> - Fixed a binary serialization alignment issue which caused Dixie to fail
> earlier
> - I've refactor and simplified the grammar per Evan's input by adding a
> tsmatch_opts rule.
> - Also updated the docs per Evan's comments
>
>
>