Ask Your Question
2

How can Knex be used to search within an array in a Postgres jsonb column?

asked 2023-07-21 18:22:59 +0000

david gravatar image

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2023-07-21 18:41:02 +0000

lakamha gravatar image

To search within an array in a Postgres jsonb column using Knex, you can use the knex.raw() method to write a raw SQL query that utilizes the @> operator.

Here's an example:

const searchTerm = 'apple';

knex('myTable')
  .select()
  .whereRaw(`myColumn::jsonb @> '[{"name": "${searchTerm}"}]'`)
  .then(results => {
    console.log(results);
  })
  .catch(error => {
    console.error(error);
  });

In this example, we're searching for the string "apple" within a JSON array stored in the myColumn column of the myTable table. We're using knex.raw() to write a raw SQL query that casts the myColumn column to jsonb, then uses the @> operator to search for an object in the array that has a name property equal to the searchTerm variable. The resulting query will look something like this:

SELECT * FROM myTable WHERE myColumn::jsonb @> '[{"name": "apple"}]'

Note that the @> operator checks if the left-hand operand (in this case, myColumn::jsonb) contains the right-hand operand (in this case, the JSON array '[{"name": "apple"}]'), so it will match any array that contains an object with a name property equal to "apple". If you need to match an exact array (i.e. an array that contains only the object {"name": "apple"}), you can use the = operator instead of @>:

const searchTerm = 'apple';

knex('myTable')
  .select()
  .whereRaw(`myColumn::jsonb = '[{"name": "${searchTerm}"}]'::jsonb`)
  .then(results => {
    console.log(results);
  })
  .catch(error => {
    console.error(error);
  });

This will generate a query like:

SELECT * FROM myTable WHERE myColumn::jsonb = '[{"name": "apple"}]'::jsonb
edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss

Add Answer


Question Tools

Stats

Asked: 2023-07-21 18:22:59 +0000

Seen: 17 times

Last updated: Jul 21 '23