Revision history [back]

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