Applying filter for type Array field

[radsal]

Steps to reproduce

Create a model with property type array:
e.g:

"propertyKey": {
      "type": [
        "string"
      ]
    }

Insert some records.
Try to filter the record: {where: { propertyKey: "somevalue"}}

Current Behavior

It doesn't return any records for the filter.
However, using loopback-connector-mongodb@5.2.3 package returns the desired response.

Expected Behavior

It should return matching records.

Link to reproduction sandbox

https://github.com/radsal/loopback-sandbox

Additional information

darwin x64 10.19.0

loopback@3.27.0
loopback-boot@3.3.1
loopback-component-explorer@6.5.1
loopback-connector-mongodb@5.2.3
loopback-connector-postgresql@5.0.1

Related Issues

See Reporting Issues for more tips on writing good issues

strongloop/loopback#4272

[radsal]

Seems like the issue is that the column is created as type text:

CREATE TABLE "public"."teams" (
  "name" TEXT NOT NULL,
  "manager" TEXT NOT NULL,
  "players" TEXT,
  "id" SERIAL,
  PRIMARY KEY("id")

[dhmlau]

@agnes512, you were looking into filters recently. Could you please help? Thanks.

[agnes512]

Hi, could you try to specify the dataType field of the property?

"propertyKey": {
      "type": [
        "string"
      ],
      "postgresql":{
            "dataType": "varchar[]",
       }
    }

After you do npm run migration, that column should have data_type ARRAY.

[radsal]

Hi, following your suggestion the column does have datatype character varying[] however the filter {where: { propertyKey: "somevalue"}} fails with parse error.
Is there an example for such filter?

[agnes512]

@radsal I checked the package, and you are right, PostgreSQL connector doesn't support filtering on arrays, see #342. I thought the operator inq would work. But seems like it only works for non-array properties. And based on #428, I think array only works with CRUD operations.

I believe it's a valid use case, not sure how much effort it needs tho D:

For other filters, we currently added these pages under Querying data.

[agnes512]

For anyone would like to contribute: the where filter and also its inq operator don't handle the case for arrays, see code

For example, to return instances that its array contains any provided values in the filter, the connector currently uses IN operator in the SQL, e.g SELECT .. FROM "public"."customer" WHERE "description" IN (...) ORDER BY "id", where description has type array, which would cause Malformed Array Literal error.

For such cases for arrays, the SQL should be
SELECT * FROM "public"."customer" WHERE 'check'=ANY("description")
or
SELECT * FROM "public"."customer" WHERE "description" @> ARRAY['check']::varchar[]

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.