sequelize: Error when using `$contains` operator with JSONB array field

Hello, I have a table with a field doc which is of type JSONB. Each doc has a browsers array. I try to search for rows where doc->browsers contains some value using the $contains operator, like this:

model.find({where: {
  doc.browsers = {
    $contains: [browserName]
  }
}})

And got this error:

ERROR:  operator does not exist: text @> text[] at character 478
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Do you know how to support this use case in Sequelize? Thanks!

About this issue

Original URL
State: open
Created 8 years ago
Reactions: 16
Comments: 44 (14 by maintainers)

Most upvoted comments

really need this bug fixed…

+10

marol-hadidjaja on Mar 14, 2016

In PostgreSQL, Can be used $contains operator with JSONB ARRAY by…

Data Format

{
  "doc": {
    "browsers": ["foo", "bar"]
  }
}

In Sequelize

model.find({
  where: {
    'doc.browsers::jsonb': {
      $contains: '["foo"]'
    }
  }
}

query

SELECT * FROM model WHERE CAST((doc #>> '{browsers}') AS JSONB) @> '["foo"]'

update 2022.04.07

if you want to use an index, you shouldn’t use the code above. In my case, I just use literal.

Data Format

{
  "doc": {
    "browsers": ["foo", "bar"]
  }
}

In Sequelize

import { literal, Op } from 'sequelize'

model.find({
  where: {
    [Op.and]: [
      literal(`("Model"."doc" -> 'browsers') @> '["foo"]'`
    ]
  }
}

haracejacob on Apr 7, 2022

Given json structure

SELECT myJsonCol FROM myTable

{locked: true, browsers: [{name: "chrome"}, {name: "firefox"}] }

I can get rows having name = "chrome" in browsers array using below query

model.find({
  where: {
    myJsonCol: {
      '$contains': { browsers:[{name: "chrome"}] }
    }
  }
})

Here is the generated SQL statement

SELECT  ...
FROM "myTable"
WHERE "myTable"."myJsonCol" @> '{"browsers":[{"name":"chrome"}]}';

duylam on Feb 28, 2016

Encountered this issue myself today in MySQL with a JSON array. My current workaround is:

const Sequelize = require('sequelize');
const { fn, col } = Sequelize;

Users.find({
  where: fn('JSON_CONTAINS', col('permissions'), '"SOME_PERMISSION"'),
});

This will find Users with permissions field that contain the value “SOME_PERMISSION”. For example user with: [“SOME_PERMISSION”, “OTHER_PERMISSION”], will be found.

This is only a workaround, I would prefer a solution to get $contains to work directly with MySQL JSON data fields.

mattickx on Apr 7, 2018

Keep it open

yocontra on Sep 19, 2017

please add good documentation with good examples for jsonb fields, it’s not clear, and the only way is to hack source

webmobiles on May 26, 2018

@samuelgoldenbaum

I’m just suggesting one way. If you consider performance, don’t put JSONB ARRAY on where clause.

haracejacob on Nov 12, 2019

This appears to be an issue for JSONB in MySQL as well, so +1

jgentes on Dec 17, 2017

my current workaround:

model.find({where: {
  doc.browsers = {
    $contains: sequelize.cast('["' + browserName + '"]', 'jsonb')
  }
}})

zaggino on Mar 24, 2016

Could you open another issue for that? It’s a valid issue, and the related regexp needs to be improved, but let’s keep this issue strictly about automatically casting to JSONB for this scenario

Edit: the other issue is #14410

ephys on Sep 24, 2022

@vkm912 There is no JSONB field in MySQL, only JSON DataType is supported in MySQL/Sequelize. Therefore his solution will not work with the MySQL dialect setup in Sequelize.

You could find a workaround for almost anything using ‘literal’ and ‘fn’ following the JSON MySQL documentation.

But it would be nice to see it being supported natively in Sequelize.

mattickx on Oct 17, 2018

as @contra mentioned, the most correct query looks like:

This is the way how I managed to make it using the sequelize query builder:

.findAll({
        where: Sequelize.and(
          {
            meta: {
              fieldOne: {
                [Op.eq]: true,
              },
            },
          },
          Sequelize.literal(`("ModelName"."meta"#>'{fieldTwo}') ?& ARRAY${JSON.stringify(['VALUE']).replace(/"/g, '\'')}`),
        ))

For me it’s also very interesting how can you receive the nested field not as a text but as jsonb. So the default approach Op.includes should work.

What is also complex - to stringify javascript array to the postgresql array. I haven’t found any suitable sequelize method.

PavelPolyakov on May 31, 2018

Also experienced that issue: { $not: { $contains: [ 1, 2, 3 ] } } is broken

flamewow on Oct 26, 2017

Also noticed that { $not: { $contains: [ 1, 2, 3 ] } } is broken even with this bug fixed internally.

Error: Invalid value [object Object]
    at Object.escape (/Users/contra/Projects/staeco/node_modules/sequelize/lib/sql-string.js:50:11)
    at Object.escape (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:919:22)
    at Object._whereParseSingleValueObject (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2374:41)
    at Object.whereItemQuery (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2076:21)
    at Utils.getOperators.forEach.op (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2210:25)
    at Array.forEach (<anonymous>)
    at Object._traverseJSON (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2208:32)
    at _.forOwn (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2188:12)
    at /Users/contra/Projects/staeco/node_modules/lodash/lodash.js:4944:15
    at baseForOwn (/Users/contra/Projects/staeco/node_modules/lodash/lodash.js:3001:24)
    at Function.forOwn (/Users/contra/Projects/staeco/node_modules/lodash/lodash.js:13002:24)
    at Object._whereJSON (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2187:7)
    at Object.whereItemQuery (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2064:19)
    at Utils.getComplexKeys.forEach.prop (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1939:25)
    at Array.forEach (<anonymous>)
    at Object.whereItemsQuery (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1937:35)
    at Object.getWhereConditions (/Users/contra/Projects/staeco/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2401:19)

yocontra on Oct 4, 2017

@dhylbert


var User = sequelize.define('user', {
  data: DataTypes.JSONB
});

return sequelize.sync({
  force: true,
  logging: console.log
})
  .then(() => {
    return User.create({
      data: {
        components: ['abc', 'bca', 'bac']
      }
    })
  })
  .then(() => {
    return User.findAll({
      where : {
        data: {
          '$contains': { components: [ 'abc' ]}
        }
      }
    });
  })
  .then(console.log)
  .finally(() => sequelize.close());

Seems to work correct for me - However, it only does exact matches, not substrings (so '$contains': { components: [ 'a' ]} does not work)

janmeier on Oct 25, 2016