[SPIKE] Content Drive drive/search: searching a content type's user-searchable fields (DB-first)

Goal (Spike — investigation only)

When exactly one content type is selected in the Content Drive, let the user
filter the listing by that content type's searchable fields — the per-field
inputs from the legacy Search portlet.

Endpoint in scope: POST /api/v1/drive/search (extend DriveRequestForm).
This is a DB-first filter: it must run inside the drive's existing SQL path
(BrowserAPIImpl.selectQuery) and must not force the request onto Elasticsearch.

This is a spike: it produces a design, not an implementation. Two outputs:

The request contract — the new field(s) on drive/search.
A DB-first feasibility note — sample SQL, per-field-type match matrix,
and the Postgres indexing/extension needs.

What "searchable field" means

A field the editor flagged as searchable: Field.searchable() (modern) /
Field.isSearchable() (legacy). Enumerate them with
com.dotcms.util.filtering.contenttype.field.UserSearchableSpecification.
Confirm whether an endpoint already returns a content type's searchable fields so
the FE can render the inputs; if not, that's a follow-up ticket.

How it works DB-first

Field values live in the contentlet c table that buildSelectBaseQuery already
joins, stored as JSON in the contentlet_as_json column. Extract a field value with:

c.contentlet_as_json -> 'fields' -> '<fieldVar>' ->> 'value'

The new filter adds one AND clause per submitted field, binding values with ?
the same way appendFilterQuery / appendMIMETypeQuery already do. Per-field-type
match semantics:

Field type	SQL match
text / textArea / custom	`... ->> 'value' ILIKE ?` (`%value%`)
select / radio / boolean	`... ->> 'value' = ?` (stored value, not label)
date / time / number	range: `>= ?` / `<= ?` (encode as `from TO to`)
multiSelect / checkbox	CSV containment on `->> 'value'`
tag	subselect on `tag` / `tag_inode`
category	subselect on `tree` / `inode`

Suggested v1: text family + select/radio/boolean + date/number ranges.
Defer tag, category and relationships to a follow-up.

Behavior must be regression-safe: when no field filter is sent, the query is
byte-for-byte unchanged (same guarantee as the workflow filter). The field filter
must not flip isUseElasticSearchForTextFiltering on.

Example of the generated query

For a request filtering the blog content type by title (text, contains
"spike") and category field topic = news, the appended clauses look like:

-- ... existing buildSelectBaseQuery() base + joins on contentlet c ...
AND c.contentlet_as_json -> 'fields' -> 'title' ->> 'value' ILIKE ?   -- '%spike%'
AND c.contentlet_as_json -> 'fields' -> 'topic' ->> 'value' = ?       -- 'news'
-- ... existing ORDER BY / LIMIT / OFFSET ...

Bound parameters (in order): ['%spike%', 'news'].

A date range (publishDate = 2026-01-01 TO 2026-06-30) becomes two clauses on
the same expression:

AND (c.contentlet_as_json -> 'fields' -> 'publishDate' ->> 'value')::timestamp >= ?  -- 2026-01-01
AND (c.contentlet_as_json -> 'fields' -> 'publishDate' ->> 'value')::timestamp <= ?  -- 2026-06-30

Multiple field inputs are joined with AND (each input narrows the result).

When NOT to offer field search

Some searchable fields are a poor fit for a DB-first per-field filter — the spike
should exclude them from the rendered inputs (or document them as unsupported):

Binary / image / file fields — the JSON holds a path/metadata, not searchable
user text.
WYSIWYG and Story Block (block editor) — values are large HTML/JSON blobs;
substring ILIKE over them is noisy and slow. Leave these to the free-text /
Elasticsearch path.
Layout-only fields (line/tab dividers, columns, rows) and constant /
hidden fields — no user-entered value to match.
Relationship fields — child-id matching needs joins; deferred past v1.
Tag / category — supported eventually via subselects, but heavier; keep out
of v1.

Postgres extensions & indexing

Extracting a JSON value is cheap; matching it at drive scale is not, unless indexed.

Equality / range (=, <, >) on select/radio/bool/date/number:
a plain B-tree functional index on the extracted expression is enough — no
extension required.
```
CREATE INDEX idx_ct_myfield
  ON contentlet ((contentlet_as_json -> 'fields' -> 'myField' ->> 'value'));
```

Substring / case-insensitive (ILIKE '%x%') on text fields: a B-tree index
can't accelerate a leading-wildcard ILIKE. Use the pg_trgm extension,
which adds trigram GIN indexes that Postgres can use for ILIKE/LIKE/~*.

CREATE EXTENSION IF NOT EXISTS pg_trgm;          -- one-time, per database

CREATE INDEX idx_ct_myfield_trgm
  ON contentlet
  USING GIN ((contentlet_as_json -> 'fields' -> 'myField' ->> 'value') gin_trgm_ops);

Version support: pg_trgm ships with Postgres since 9.1 and JSONB/GIN
since 9.4, so both are available on every Postgres version dotCMS supports —
no upgrade needed. pg_trgm is a contrib module bundled with the standard
Postgres distribution (and enabled by default on the dotCMS image / RDS / Cloud
SQL / Azure), so CREATE EXTENSION is the only step.

The spike should note: who creates these indexes (per-field, on demand vs. a fixed
set), and whether v1 ships without trgm indexes (correct but slower) and adds them
as a tuning follow-up.

Contract to define on `drive/search`

Request shape on DriveRequestForm. A map keyed by field variable is the
natural fit, e.g.:

{
  "contentTypes": ["blog"],
  "searchableFields": {
    "title":       "spike",
    "topic":       "news",
    "publishDate": "2026-01-01 TO 2026-06-30"
  }
}

How a field is identified (velocity variable name), and how multi-value
(multiSelect/checkbox) and ranges (from TO to) are encoded.
AND vs OR across multiple field inputs, and how this composes with the
existing free-text filters.text.
Where the single-content-type rule is enforced — FE only, or BE validation?

Deliverable

A short write-up (comment on this issue or a linked doc) with:

the proposed drive/search request contract (with the JSON example), and
the DB-first note: a sample SQL clause, the per-field-type matrix, the v1 type
scope, the "not recommended" list, and the indexing/extension plan above.

Plus a recommendation on the contract shape and the follow-up implementation
tickets to open. No code changes in this spike.

Out of scope

Multi-content-type field search (single-CT only).
The implementation, the FE input UI, and tests (separate tickets once the
spike lands).

Spike conclusion — searchable-field search in Content Drive: DB-first is viable and scalable on Postgres for the bounded 1-CT case; ES/OS remains the default for everything else

🟢 TL;DR

Searching a content type's searchable fields can be done DB-first in the
drive's existing SQL path, and it scales on Postgres — but only under a
clear premise: the field must be indexed (GIN), the term must be ≥3 chars
for substring, and you accept the GIN write/storage cost. The real work is not
the SQL — it's governing which fields get indexed and when (machinery that
doesn't exist today).

DB-first ✅ for the drive case: one content type, scalar/text fields,
on an indexed field. Bonus: read-your-writes consistency (no index
refresh lag) for the "filter right after editing" case.

ES/OS ✅ default for: multi-CT, relevance/faceting, and non-scalar types
(Tag, Category, Relationship, StoryBlock) — covered for free since
searchable ⇒ indexed.

1. Current index state on `contentlet_as_json`

Only one index exists: idx_contentlet_template_value — a btree functional index, field template only, serves = and prefix (LIKE 'x%'). Created by Task241014AddTemplateValueOnContentletIndex (postgres.sql:2370). The functional-index-over-a-JSON-field pattern is already in production.
No GIN of any kind on contentlet — neither containment (jsonb_ops/jsonb_path_ops) nor trigram (gin_trgm_ops).
⇒ Today, equality/containment and contains on any field other than template = seq scan. Any "doesn't scale" numbers reflect this un-indexed baseline, not the ceiling.

For reference, GIN indexes do exist elsewhere in the schema — job(parameters), job(result), and at runtime unique_fields(supporting_values->'contentletIds') (UniqueFieldDataBaseUtil, created CONCURRENTLY). So building GIN indexes on jsonb expressions at runtime is already an established pattern in the codebase.

2. Indexing strategy (all native Postgres, with precedent in the repo)

-- (a) Equality / containment of ALL fields with ONE index
CREATE INDEX CONCURRENTLY idx_contentlet_fields_gin
  ON contentlet USING GIN ((contentlet_as_json->'fields') jsonb_path_ops);

-- (b) Substring (contains) per HOT field — only where needed
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_contentlet_<ct>_<field>_trgm
  ON contentlet USING GIN ((contentlet_as_json->'fields'->'<field>'->>'value') gin_trgm_ops);

-- (c) Equality / prefix on a scalar field (same shape as the existing template index)
CREATE INDEX CONCURRENTLY idx_contentlet_<ct>_<field>
  ON contentlet ((contentlet_as_json->'fields'->'<field>'->>'value'));

What trigram (pg_trgm) buys us: a trigram is a 3-character group; pg_trgm precomputes every value's trigrams into a GIN index, so ILIKE '%term%' becomes an indexed lookup instead of a seq scan. It's case-insensitive (so ILIKE works directly), at the cost that terms shorter than 3 chars can't use it. pg_trgm ships with Postgres since 9.1 (and jsonb_path_ops GIN since 9.4), so both are available on every Postgres version dotCMS supports — CREATE EXTENSION is the only step.

3. Is it scalable? — yes, under these premises

It is scalable only if all hold (it is not automatic):

The field has a GIN index (no index = seq scan = does not scale).
Substring terms are ≥3 chars (trigram needs a full 3-char group; %ab% degrades).
The GIN write/storage cost is accepted (trigram GIN is large, ≳ table size, and slows every save/bulk reindex — measure it).
The field type is covered (text/scalar). Non-scalar types (Tag/Category/Relationship/StoryBlock) still need JOINs/recursion → route to ES/OS.

4. What to implement

#	Action	Detail
1	One GIN containment index	`USING GIN ((contentlet_as_json->'fields') jsonb_path_ops)` — covers equality/containment for all fields at once
2	GIN trigram per hot field	`pg_trgm` + `gin_trgm_ops` on the field expression — enables scalable `contains`, only on frequently-searched fields
3	Index lifecycle ⚠️	Decide who declares which fields are indexed and when they're created (at field definition / on-demand / config). This machinery does not exist today — it is the main work.
4	Extend the `drive/search` contract	Map `{ctVar: {fieldVar: value}}`, validate single-CT in the backend, enforce minimum search length (≥3) for substring
5	Build the SQL in `BrowserAPIImpl.selectQuery`	Per-type clauses over `contentlet_as_json` with bound params; join `contentlet_version_info` (variant + language + `deleted=false`); `LIMIT` + keyset pagination; select `c.inode` only
6	Route the rest to ES/OS	Multi-CT, relevance/faceting, StoryBlock full-text, non-scalar types

Per-field-type SQL shapes (DB-first, scoped to one CT; ? = bound param):

-- Text contains (indexed by trigram GIN)
AND (c.contentlet_as_json->'fields'->'title'->>'value') ILIKE '%' || ? || '%'
-- Text equality / prefix
AND (c.contentlet_as_json->'fields'->'title'->>'value') = ?
AND (c.contentlet_as_json->'fields'->'title'->>'value') LIKE ? || '%'
-- Select / Radio — equality by VALUE (not label), covered by containment GIN
AND (c.contentlet_as_json->'fields'->'status'->>'value') = ?
-- Number — range (CAST required)
AND (c.contentlet_as_json->'fields'->'readTime'->>'value')::numeric BETWEEN ? AND ?
-- Date — range (ISO-8601 UTC sorts chronologically)
AND (c.contentlet_as_json->'fields'->'publishDate'->>'value') BETWEEN ? AND ?

Non-scalar types (Tag → side-table JOIN, Category → tree JOIN, Relationship → version re-resolution, StoryBlock → recursive ProseMirror extraction) are not in the DB-first scope — route to ES/OS.

5. Verdict

DB-first is the right tool for the Content Drive's bounded case — one content
type, scalar/text fields, on an indexed field — and it scales on Postgres
thanks to GIN (one containment index for equality, trigram per hot field for
contains). It also gives read-your-writes consistency that the index path
(with its refresh lag) cannot. ES/OS stays the default for multi-CT,
relevance, and non-scalar types, which are already indexed for free
(searchable ⇒ indexed).

The decision is not "can the DB do it?" (on Postgres, yes) but "which
fields are worth indexing by hand vs. what ES/OS already gives for free." The
hard part of the implementation is the index lifecycle (§4.3), not the SQL.

Non-functionals if DB-first is adopted: always join contentlet_version_info (variant + language + deleted=false); LIMIT + keyset pagination; select c.inode only; statement_timeout + slow-query logging; create GIN/functional indexes CONCURRENTLY, on-demand, only for hot fields; enforce a minimum search length (≥3 chars) for substring; measure GIN write amplification on bulk reindex before rollout.

Follow-up tickets: (1) define the drive/search contract ({ctVar:{fieldVar:value}}, single-CT validated in BE, min-length rule); (2) design the index lifecycle for searchable fields (who declares hot fields, when indexes are created — the core work); (3) wire DB-first field clauses into BrowserAPIImpl.selectQuery; (4) route multi-CT / non-scalar / relevance to the existing ES/OS SearchAPI path.

Ref: https://dotcms.slack.com/docs/T028R2630/F0BB6EP52AZ

Navigation Menu

Repository navigation

[SPIKE] Content Drive drive/search: searching a content type's user-searchable fields (DB-first) #35923

Goal (Spike — investigation only)

What "searchable field" means

How it works DB-first

Example of the generated query

When NOT to offer field search

Postgres extensions & indexing

Contract to define on `drive/search`

Deliverable

Out of scope

Spike conclusion — searchable-field search in Content Drive: DB-first is viable and scalable on Postgres for the bounded 1-CT case; ES/OS remains the default for everything else

🟢 TL;DR

1. Current index state on `contentlet_as_json`

2. Indexing strategy (all native Postgres, with precedent in the repo)

3. Is it scalable? — yes, under these premises

4. What to implement

5. Verdict

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[SPIKE] Content Drive drive/search: searching a content type's user-searchable fields (DB-first) #35923

Description

Goal (Spike — investigation only)

What "searchable field" means

How it works DB-first

Example of the generated query

When NOT to offer field search

Postgres extensions & indexing

Contract to define on drive/search

Deliverable

Out of scope

Activity

fabrizzio-dotCMS commented on Jun 11, 2026

Spike conclusion — searchable-field search in Content Drive: DB-first is viable and scalable on Postgres for the bounded 1-CT case; ES/OS remains the default for everything else

🟢 TL;DR

1. Current index state on contentlet_as_json

2. Indexing strategy (all native Postgres, with precedent in the repo)

3. Is it scalable? — yes, under these premises

4. What to implement

5. Verdict

zJaaal commented on Jun 17, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Contract to define on `drive/search`

1. Current index state on `contentlet_as_json`