Postgresql column as text[] or character varying() is returning data as string

[prernagp]

I have created a model that has an array field of categories. Here is the property definition:-

@property({
    type: 'string',
    postgresql: { columnName: 'categories', dataType: 'ARRAY', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES' },
  })
  categories?: string;

And when I apply filters like this:

 const filter1: any = {
      where: {
        categories: { contains: ['ViewLibrarySong'] },
      },
    };
    return this.blogRepository.find(filter1);

I am getting value of the column as string here. i.e. operatorValue's value is '["ViewLibrarySong"]'.

case 'contains':
      return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map((v) => `'${v}'`) + ']::'
        + propertyDefinition.postgresql.dataType);

The application is getting crashed as map will not work on string.

[samarpan-b]

I think this is related to #460

@bajtos , @shubhisood any ideas ? Can someone please help ? We are stuck on a feature around this.

[bajtos]

@samarpan-b did you perhaps forget to add allowExtendedOperators: true to your model settings?

See the docs: https://github.com/strongloop/loopback-connector-postgresql/tree/f0afde2d62d52c2f3867b6102ed2e8ee37b9d1e5#extended-operators

[samarpan-b]

@prernagp can you please try this and confirm ?

[prernagp]

@bajtos We are using allowExtendedOperators: true
We have made it work by changing the definition of categories to the following:-

@property({
    type: 'array',
    itemType: 'string',
    postgresql: {
      columnName: 'categories',
      dataType: 'varchar[]',
      dataLength: null,
      dataPrecision: null,
      dataScale: null,
      nullable: 'YES',
    },
  })
  categories?: string[];

But there is one issue I have seen that it is working as a AND operator. For example:-
This filter will only extract those rows which have these both categories:-

where: {
        categories: { contains: ['ViewLibrarySong', 'CreateLibrarySong'] },
  },

It should work as an OR operator.

[prernagp]

@bajtos Can you please help us here?

[bajtos]

Here is the query triggered by contains operator:

https://github.com/strongloop/loopback-connector-postgresql/blob/463becbce7c446a9d865b85e0513d93fd6374190/lib/postgresql.js#L537-L539

I am not an expert on PostgreSQL, this is what I found in the docs:

https://www.postgresql.org/docs/current/functions-array.html

anyarray @> anyarray → boolean
Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[1] and ARRAY[1,1] are each considered to contain the other.)

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

I believe the current behavior is intended? I do see how it could be confusing to you as the user, I guess a better operator name would be containsAll?

But there is one issue I have seen that it is working as a AND operator.
It should work as an OR operator.

How would you write such query in raw SQL? We can discuss how to introduce a new operator similar to contains, one that would map to your SQL.

As a workaround, have you considered using LoopBack's or predicate? Something along the following lines:

const filter = {
  where: {
    or: [
        { categories: { contains: ['ViewLibrarySong'] },
        { categories: { contains: ['CreateLibrarySong'] }
    ]
  }
}

[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.