CODEWITHSUNDEEP
postgresqlnestjstypeorm
Ajinkya Ganoo
Ajinkya Ganoo

Reputation: 165

Not able to search through character varying[] array column

My postgres database table has a column location which is character varying[]. In my nestjs entity of the table I have following for the location column-

 @Column("character varying",{array:true})
    location: string[];

What I am trying to do is search the rows having passed parameter as locations. This is the raw query which is giving me appropriate results-

select * from blogs where language @> '{"Spanish","English"}'

In my nestjs service, how can I achieve the above query? I tried doing this-

return await this.blogsRepo.find({
  where: [
    {
      location: Any(body.locations)
    }
  ]
})

body.locations is an array like this-

body.locations = ["Spanish","English"]

The above typeorm solution gives me following error-

'could not find array type for data type character varying[]'

What could be the possible solution for this? I will love a typeorm solution as I have kept raw query execution as my last option.

Thanks in advance,

Upvotes: 4

Views: 1479

Answers (3)

Akram Rabie
Akram Rabie

Reputation: 545

It might be a bit late but for those who came across this question, you can achieve this by the Raw operator of Typeorm:

import { Raw } from 'typeorm';
  • If the field is a character varying field, which an example value would be like:
{value1, value2, value3}

You could do this:


const searchKey = 'value3'

sampleRepo.find({
   where: {
      columnName: Raw((alias) => ` '${searchKey}' = ANY (${alias})`),
   }
})
  • And if it is a jsonb field, which the example value would be like:
[{key1: 'value1', key2: 'value2'}, {key1: 'value21', key2: 'value22'}])

You could do this:


const searchKey = 'value1'

sampleRepo.find({
   where: {
      columnName: Raw((alias) => ` ${alias} @> '[{"key1": "${searchKey}"}]'`)
   }
})

Upvotes: 0

Ajinkya Ganoo
Ajinkya Ganoo

Reputation: 165

As per the typeorm documentations, there is no way/ built in typeorm operator equivalent to @> operator.

So the only way to achieve what I wanted is to generate an entire query and then execute it using Repository.query() method.

Seems to work so far!

Upvotes: 0

dennbagas
dennbagas

Reputation: 2993

You could try this:

await this.blogsRepo.find({
  where: `${body.locations} = ANY (location)`,
});

Upvotes: 0

Related Questions