Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Patch: Add tsmatch JSONPath operator for granular Full Text
 Search
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <CA+v5N41Rr-18cjSsc3a6YvGTmnaXmfNiQM3e_gvdw76Yj7y6zA@mail.gmail.com>
Date: Thu, 26 Feb 2026 14:48:06 +0800
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CF8199CE-C73F-458A-8A51-9CDC5DA46110@gmail.com>
References: <CA+v5N42aGz5sCa3HoURUK82b-PN6N6LtUj45vAcL7KEBd5i5fg@mail.gmail.com>
 <CA+v5N41Rr-18cjSsc3a6YvGTmnaXmfNiQM3e_gvdw76Yj7y6zA@mail.gmail.com>
To: Florents Tselai <florents.tselai@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/CF8199CE-C73F-458A-8A51-9CDC5DA46110%40gmail.com>
Precedence: bulk


> On Feb 1, 2026, at 19:02, Florents Tselai <florents.tselai@gmail.com> =
wrote:
>=20
>=20
>=20
>=20
> On Mon, Jan 26, 2026 at 7:22=E2=80=AFPM Florents Tselai =
<florents.tselai@gmail.com> wrote:
> Hi,
>=20
> 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.
>=20
> SELECT jsonb_path_query(doc, '$.comments[*] ? (@.user =3D=3D "Alice" =
&& @.body tsmatch "performance")');
>=20
> 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=E2=80=94
> solving issues with structural ambiguity and query complexity.
>=20
> 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").
>=20
> - 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.
>=20
> 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.
>=20
> SELECT jsonb_path_query(doc, '$.comments[*] ? (@.user =3D=3D "Alice" =
&& @.body tsmatch "performance")');
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> Here's a v2, that implements the tsqparser clause=20
>=20
> So this should now work too=20
>=20
> 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.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/