I'm making a search by tags function, in a table like this

    CREATE TABLE permission (
      id serial primary key,
      tags varchar(255)[],
    );

Then I add a row that has the tags "artist" and "default".

I'd like to query it by tags (using the knex query builder), so if I do this:

  async getByTags(tags: string[]): Promise<PermissionTable[]> {
    return this.db<PermissionTable>('permission')
      .select('*')
      .whereRaw("tags @> '{??}'", [tags])
  }

That produces the following statements depending on how many tags you passed.

This works

    select * from "permission" where tags @> '{"artist"}';

This doesn't (returns an empty array, when it should the row I'm looking for)

    select * from "permission" where tags @> '{"artist", "event"}';

Why does the one with multiple tags not work?

I'm making a search by tags function, in a table like this

    CREATE TABLE permission (
      id serial primary key,
      tags varchar(255)[],
    );

Then I add a row that has the tags "artist" and "default".

I'd like to query it by tags (using the knex query builder), so if I do this:

  async getByTags(tags: string[]): Promise<PermissionTable[]> {
    return this.db<PermissionTable>('permission')
      .select('*')
      .whereRaw("tags @> '{??}'", [tags])
  }

That produces the following statements depending on how many tags you passed.

This works

    select * from "permission" where tags @> '{"artist"}';

This doesn't (returns an empty array, when it should the row I'm looking for)

    select * from "permission" where tags @> '{"artist", "event"}';

Why does the one with multiple tags not work?

• javascript
• sql
• postgresql
• typescript
• knex.js

• Using ?? replacement there has SQL injection hole. Those are meant only for passing identifier names with correct quotes to the query. – Mikael Lepistö Commented May 7, 2020 at 9:04

1 Answer 1

@> is the "contains" operator so a1 @> a2 is true when a1 contains all the elements in a2. For example:

array['artist', 'default'] @> array['artist']            -- True
array['artist', 'default'] @> array['default', 'artist'] -- True
array['artist', 'default'] @> array['artist', 'event']   -- False

So @> is checks if the right operand is a set-wise subset of the left operand.

I think you're looking for the "overlaps" operator:

&&
overlap (have elements in mon)
ARRAY[1,4,3] && ARRAY[2,1] -- True

This operator checks if the set-wise intersection of the left and right operands is non-empty.

Something like:

.whereRaw("tags && '{??}'", [tags])

should server you better.