1

I am trying to update an existing postgres query in the Nodejs code base.

Below is the existing query to select based on name

constructor(schema){
 this.table = schema.employee_tb;
}

row = await this.table.findOne({ name });

Below is the Postgres query I am trying to translate and update the above code into...

select * from table where name = 'john' and '3467' = ANY(group_number)

I tried

row = await this.table.findOne({ name : 'john' && ANY(group_number): '3467' });

For which it is throwing an error.

Basically, I am trying to have the ANY operator in using the massive library.

NOTE: group_number is character_varying[] which has data like {3467, 3455, 3421}..... the query should return the row which contains the group number in the group_number column.

Please help, Thanks in advance.

Geezus
  • 21
  • 4
  • Errors usually contain very important information, if you work with a serious library. Can you add it please? – Wu Wei Jan 31 '23 at 22:36
  • it doesn't even let me run it. It says " ',' expected at ANY(group_number)^: '3467' " – Geezus Jan 31 '23 at 22:42
  • Ha, yeah sure. `{ name : 'john' && ANY(group_number): '3467' }` is not valid Javascript. What library are you using, that gives you the findOne function? – Wu Wei Jan 31 '23 at 22:51
  • @Geezus Please add the information about the node library the `findOne` function is from. That will allow people to answer – Matt Jan 31 '23 at 23:56
  • @Matt & anarchist912 The library is Massive. – Geezus Feb 01 '23 at 15:59

2 Answers2

0

If you are looking to findOne that has the name 'john' AND the group_number '3467', you need to update your object syntax.

row = await this.table.findOne({ name : 'john', group_number : '3467' });

If you are using Sequelize here for the findOne method, I would take a look at the docs here where you can add in the ANY operator with Op.any in your query, but you will need to have that character_varying[] ready to pass into the function.

Kyle Combs
  • 106
  • 1
  • 8
  • Hi Kyle this would work if the group_number is not an array! Here group_number is character_varying[] which has data like {3467, 3455, 3421,} – Geezus Jan 31 '23 at 23:15
  • @Geezus where is the postgres query that you are trying to convert coming from? The ANY operator in postgres takes in another query as it's argument. https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-any/ – Kyle Combs Feb 01 '23 at 01:02
  • not necessarily. The ANY operator can also be used to see if an array contains a particular value .... in the above query we are trying to find if the group_number contains 3467. – Geezus Feb 01 '23 at 01:51
  • I am using a Massive Library. – Geezus Feb 01 '23 at 16:07
  • Is `group_number` a column in your table or is it it's own table? – Kyle Combs Feb 01 '23 at 17:23
  • its a column in the same table. – Geezus Feb 01 '23 at 17:24
  • so you're just looking for the first record where the name is John and the group_number is '3467'? Or are you storing arrays of strings in the group_number column? – Kyle Combs Feb 01 '23 at 17:31
  • we have an array of strings in the group_numbercolumns! – Geezus Feb 01 '23 at 20:08
0

This worked!!

row = await this.table.where(`name = 'jhon' and '3467' = ANY(group_number)`);
Matt
  • 68,711
  • 7
  • 155
  • 158
Geezus
  • 21
  • 4