Ecommerce Template Shop Search Bug When Using PostgreSQL

[alibaddd]

Describe the Bug

In the ecommerce template, the shop search implementation applies a like filter to the description field, which is defined as richText. In Payload, richText fields are stored as JSON (Lexical), and when using the PostgreSQL adapter (@payloadcms/db-postgres), this maps to a jsonb column.

In app/(app)/shop/page.tsx, the following query is used when a search value is present:

{
  or: [
    {
      title: {
        like: searchValue,
      },
    },
    {
      description: {
        like: searchValue,
      },
    },
  ],
}

With PostgreSQL, this results in SQL similar to:

"products"."description" ILIKE '%t%'

Since description is stored as jsonb, PostgreSQL throws a runtime error:

operator does not exist: jsonb ~~* unknown

This causes the /shop page to fail whenever a search query is provided when using PostgreSQL.

This is primarily a template bug: the ecommerce template uses a like operator on a richText field, which is not compatible with how the field is stored in PostgreSQL.

Suggested fix:

Rich text fields are stored as JSON, so they should not be queried directly with string operators like like. Instead, introduce a derived plain-text field that contains the searchable text extracted from the rich text value, and query that field.

Add a hidden field to the collection:

{
  name: 'descriptionPlainText',
  type: 'textarea',
  admin: {
    hidden: true,
    readOnly: true,
  },
}

Populate it using a collection-level beforeChange hook:

import { convertLexicalToPlaintext } from '@payloadcms/richtext-lexical/plaintext'

hooks: {
  ...defaultCollection?.hooks,
  beforeChange: [
    ...(defaultCollection?.hooks?.beforeChange || []),
    ({ data }) => {
      if (data?.description) {
        data.descriptionPlainText = convertLexicalToPlaintext({
          data: data.description,
        })
      } else {
        data.descriptionPlainText = ''
      }

      return data
    },
  ],
},

Then update the search query in app/(app)/shop/page.tsx to use the derived field instead of description:

{
  or: [
    {
      title: {
        like: searchValue,
      },
    },
    {
      descriptionPlainText: {
        like: searchValue,
      },
    },
  ],
}

I can open a PR implementing this approach (already tested locally) if this direction makes sense. Happy to adjust based on feedback.

Link to the code that reproduces this issue

https://github.com/payloadcms/payload/tree/main/templates/ecommerce

Reproduction Steps

1. Create a new Payload app using the ecommerce template with PostgreSQL:

   npx create-payload-app@latest my-app -t ecommerce

2. Start the app:

   pnpm dev

3. Open http://localhost:3000, click Seed, and create an admin account

4. Visit the shop page with a search query:

   http://localhost:3000/shop?q=t
   

5. Observe the server logs / terminal output

Result: the request fails with a PostgreSQL error:

operator does not exist: jsonb ~~* unknown

Which area(s) are affected?

area: templates

Environment Info

Binaries:
  Node: 22.22.2
  npm: 10.9.7
  Yarn: 1.22.22
  pnpm: 10.33.0
Relevant Packages:
  payload: 3.81.0
  next: 16.2.1
  @payloadcms/db-postgres: 3.81.0
  @payloadcms/drizzle: 3.81.0
  @payloadcms/email-nodemailer: 3.81.0
  @payloadcms/graphql: 3.81.0
  @payloadcms/live-preview: 3.81.0
  @payloadcms/live-preview-react: 3.81.0
  @payloadcms/next/utilities: 3.81.0
  @payloadcms/plugin-form-builder: 3.81.0
  @payloadcms/plugin-seo: 3.81.0
  @payloadcms/richtext-lexical: 3.81.0
  @payloadcms/translations: 3.81.0
  @payloadcms/ui/shared: 3.81.0
  react: 19.2.4
  react-dom: 19.2.4
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Thu, 26 Mar 2026 19:20:28 +0000
  Available memory (MB): 64226
  Available CPU cores: 16