PG LIKE search within isArray columns

[TrilipuT]

Hey there!

I have one issue that i have solved but seems like I didn't get to the cause of it.
So when I'm using isArray for field and substringSearch: true it sets additional " into value
See 063 in the query.

🪲📜 PG Q: SELECT "id", "plate", "name", "phones", "status", "type", "building", "flat", "created_at", "updated_at", "expire_at", "comments", "is_us_plate" FROM "vehicle" WHERE "phones" ILIKE $1 AND "status" IS DISTINCT FROM $2 AND "building" = $3 ORDER BY "id" DESC LIMIT $4 OFFSET $5 params: [ '%"063"%', 2, 'b4', 50, 0 ]

🪲📜 PG Q: SELECT COUNT(*) FROM "vehicle" WHERE "phones" ILIKE $1 AND "status" IS DISTINCT FROM $2 AND "building" = $3 values: [ '%"\\"063\\""%', 2, 'b4' ]

so seems like those quotes are ambigious for like query.
I found only one way is to replace it. In file postgres data connector. But i think that might have a better way to solve it.

if (filter.operator == AdminForthFilterOperators.LIKE || filter.operator == AdminForthFilterOperators.ILIKE) {
    let value = filter.value.replaceAll('"','');
    value = value.replaceAll('\\','');
    return [`%${value}%`];
}

Here's my column setup

{
    name: 'phones',
    type: AdminForthDataTypes.JSON,
    isArray: {
        enabled: true,
        itemType: AdminForthDataTypes.STRING,
    },
    minLength: 12,
    filterOptions: {
        debounceTimeMs: 800,
        substringSearch: true,
    },
}

Please let me know if that can be fixed somehow...
Thanks!

[SerVitasik]

Hi, thank you for the issue and for sharing the details! We’ll take a look and see what we can do.

[SerVitasik]

Also could you send me your SHOW CREATE TABLE? Because I have been trying with this

CREATE TABLE IF NOT EXISTS pg_apartments (
      id TEXT PRIMARY KEY,
      created_at TIMESTAMP,
      phones JSONB,
      ...

It gives my this queries:

🪲📜 PG Q: SELECT "id", "created_at", "title", "square_meter", "price", "number_of_rooms", "room_sizes", "phones", "description", "country", "listed", "realtor_id", "user_id", "apartment_image", "apartment_source" FROM "pg_apartments" WHERE "phones" ILIKE $1  LIMIT $2 OFFSET $3 params: [ '%050%', 25, 0 ]
🪲📜 PG Q: SELECT COUNT(*) FROM "pg_apartments" WHERE "phones" ILIKE $1 values: [ '%050%' ]

[TrilipuT]

thank you for the answer!
I was using wrong column type. Changed and re-generated migrations. Now I have next setup

-- CreateTable
CREATE TABLE "vehicle" (
    "id" SERIAL NOT NULL,
    "plate" TEXT,
    "status" INTEGER NOT NULL DEFAULT 2,
    "type" INTEGER NOT NULL DEFAULT 1,
    "name" TEXT,
    "building" TEXT NOT NULL,
    "flat" TEXT NOT NULL,
    "phones" JSONB NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "expire_at" TIMESTAMP(3) NOT NULL,
    "comments" TEXT,
    "is_us_plate" BOOLEAN NOT NULL DEFAULT false,
    "data" JSONB,

    CONSTRAINT "vehicle_pkey" PRIMARY KEY ("id")
);

But currently I got the next error. I'm using postgres 18

🪲📜 PG Q: SELECT "id", "plate", "name", "phones", "status", "type", "building", "flat", "created_at", "updated_at", "expire_at", "comments", "is_us_plate" FROM "vehicle" WHERE "phones" ILIKE $1 AND "status" IS DISTINCT FROM $2 ORDER BY "id" DESC LIMIT $3 OFFSET $4 params: [ '%063%', 2, 50, 0 ]
🪲📜 PG Q: SELECT COUNT(*) FROM "vehicle" WHERE "phones" ILIKE $1 AND "status" IS DISTINCT FROM $2 values: [ '%063%', 2 ]
Error in handler error: operator does not exist: jsonb ~~* unknown
    at /Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/pg/lib/client.js:545:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PostgresConnector.getDataWithOriginalTypes (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/postgres.ts:331:22)
    at async Promise.all (index 0)
    at async PostgresConnector.getData (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/baseConnector.ts:356:27)
    at async handler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/modules/restApi.ts:736:22)
    at async expressHandler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/servers/express.ts:317:18) {
  length: 203,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_oper.c',
  line: '635',
  routine: 'op_error'
}
error: operator does not exist: jsonb ~~* unknown
    at /Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/pg/lib/client.js:545:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PostgresConnector.getDataWithOriginalTypes (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/postgres.ts:331:22)
    at async Promise.all (index 0)
    at async PostgresConnector.getData (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/baseConnector.ts:356:27)
    at async handler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/modules/restApi.ts:736:22)
    at async expressHandler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/servers/express.ts:317:18)

[TrilipuT]

Looks like I have several solutions here...

The easiest i added for now - hook that cast filed to text in my resource definition via insecureRawSQL.

hooks: {
        list: {
            beforeDatasourceRequest: async ({adminUser, query}) => {
                query.filters = query.filters.map((filter: any) => {
                    if (filter.field === 'phones') {
                        return {
                            insecureRawSQL: `phones::text LIKE '%${filter.value}%'`,
                        }
                    }

                    return filter;
                });

                return {ok: true}
            }
        },

But here's the question - when I'm registering custom api endpoint - it does not use this filter. I'm calling admin.resource('vehicle').list(filters) but it just ignores hooks...
Can this be solved somehow?

router.get(`/api/users`,
        async (req, res) => {
            let filters = [];
            for (let k in req.query) {
                if(k == 'phones'){
                    filters.push(Filters.IN('phones', [req.query[k]]))
                } else {
                    filters.push(Filters.EQ(k, req.query[k]))
                }
            }
            res.json(
                await admin.resource('vehicle').list(filters)
            );
        }
    );

[ivictbor]

@TrilipuT hey!

I was using wrong column type

Do you remember what type did you use? Probably there was a bug still, I mean - adminforth should try to support as many types as possible. Did you heve TEXT of VARCHAR for phones?

The easiest i added for now - hook that cast filed to text in my resource definition via insecureRawSQL.

Yes, if you plan to stay with JSONB, then search via JSONB will require case anyway, so your solution looks good.

I'm calling admin.resource('vehicle').list(filters)

Yes, exactly, data api does not call hooks. Please use insecureRawSQL in Data API directly, like here: https://github.com/devforth/adminforth/pull/390/files

[ivictbor]

And some video with detailed explanations about filters and hooks in data API: https://tracklify.com/video/eb6fae07-d299-4a0d-976a-4b56d945ae71

[TrilipuT]

Thanks for the explanation @ivictbor !
I understand your intention to separate custom code and admin part. On the other hand I thought if I use admin.resource('vehicle').list(filters) than it should execute list hooks from the resource I calling. But that solution also works, duplicating some hooks is not a problem.

Do you remember what type did you use? Probably there was a bug still, I mean - adminforth should try to support as many types as possible. Did you heve TEXT of VARCHAR for phones?

I used TEXT type, but probably from old scheme (I used sqlite at the beginning). What i read in the code - it should do JSON.stringify() and save the data to TEXT column...
And actually what I tested just now - String format in Prisma, JSON type and isArray in adminforth - works great. And seems like that was my mistake to choose jsonb format to store array of values. It's not intended for such cases.

But I still need rawSQL because it uses quotes and looks like this.

🪲📜 PG Q: SELECT "id", "plate", "name", "phones", "status", "type", "building", "flat", "created_at", "updated_at", "expire_at", "comments", "is_us_plate" FROM "vehicle" WHERE "phones" ILIKE $1 AND "status" IS DISTINCT FROM $2 ORDER BY "id" DESC LIMIT $3 OFFSET $4 params: [ '%"063252"%', 2, 50, 0 ]

Should it avoid quotes when using substringSearch: true ?

So I will make it easy and get back to TEXT column and leave it to adminforth to deal with data conversion.

Again, thank you for great framework!

[TrilipuT]

Also, you can check array data types in postgres, seems like adminforth does not support that.
Not sure that this should be addressed... array data type requires conversion from [] to {}. That might be too much.

What I tried just now:
schema.prisma

model vehicle {
...
  phones      String[]   // Array of phones connected to vehicle
...
}

It generates the next schema

CREATE TABLE "vehicle" (
...
    "phones" TEXT[],
...
)

When I try to save with standard resource config for such field

{
            name: 'phones',
            type: AdminForthDataTypes.JSON,
            isArray: {
                enabled: true,
                itemType: AdminForthDataTypes.STRING,
            },
            minLength: 12
}

It gives me an error of malformed array literal.

🪲📜 PG Q: INSERT INTO "vehicle" ("expire_at", "is_us_plate", "phones", "plate", "name", "status", "type", "building", "flat", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING "id" values: [
  M {
    '$L': 'en',
    '$d': 2027-10-14T15:30:57.000Z,
    '$y': 2027,
    '$M': 9,
    '$D': 14,
    '$W': 4,
    '$H': 18,
    '$m': 30,
    '$s': 57,
    '$ms': 0,
    '$x': {},
    '$isDayjsObject': true
  },
  0,
  '["380632521709","380632521701"]',
  '123123',
  '`4`2424',
  0,
  1,
  'b2',
  '1234',
  M {
    '$L': 'en',
    '$d': 2025-10-14T15:36:36.000Z,
    '$y': 2025,
    '$M': 9,
    '$D': 14,
    '$W': 2,
    '$H': 18,
    '$m': 36,
    '$s': 36,
    '$ms': 0,
    '$x': {},
    '$isDayjsObject': true
  },
  M {
    '$L': 'en',
    '$d': 2025-10-14T15:36:36.000Z,
    '$y': 2025,
    '$M': 9,
    '$D': 14,
    '$W': 2,
    '$H': 18,
    '$m': 36,
    '$s': 36,
    '$ms': 0,
    '$x': {},
    '$isDayjsObject': true
  }
]
Error in handler error: malformed array literal: "["380632521709","380632521701"]"
    at /Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/pg/lib/client.js:545:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PostgresConnector.createRecordOriginalValues (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/postgres.ts:392:6)
    at async PostgresConnector.createRecord (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/baseConnector.ts:284:19)
    at async AdminForth.createResourceRecord (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/index.ts:557:38)
    at async handler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/modules/restApi.ts:1221:30)
    at async expressHandler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/servers/express.ts:317:18) {
  length: 222,
  severity: 'ERROR',
  code: '22P02',
  detail: '"[" must introduce explicitly-specified array dimensions.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: "unnamed portal parameter $3 = '...'",
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'arrayfuncs.c',
  line: '438',
  routine: 'ReadArrayDimensions'
}
error: malformed array literal: "["380632521709","380632521701"]"
    at /Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/pg/lib/client.js:545:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PostgresConnector.createRecordOriginalValues (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/postgres.ts:392:6)
    at async PostgresConnector.createRecord (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/dataConnectors/baseConnector.ts:284:19)
    at async AdminForth.createResourceRecord (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/index.ts:557:38)
    at async handler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/modules/restApi.ts:1221:30)
    at async expressHandler (/Users/vitalynikolaiev/LocalSites/adminforth-app/node_modules/adminforth/servers/express.ts:317:18)

[SerVitasik]

Hi @TrilipuT ! We’ve added array support - please run npm run adminforth@next and try again