Sequelize Op.contains throws Unhandled rejection Error: Invalid value

Question

Given the Postgres 10 JSONB data field partner_json:

{
  "guid": "a659883cedf44131a700a6f563f2c484",
  "name": "Testing",
  "referrerId": 1,
  "communication": {
    "email": [
      {
        "value": "[email protected]",
        "primary": true,
        "emailTypeCode": 2
      }
    ],
    "phone": [
      {
        "primary": true,
        "phoneNumber": "+15705551234",
        "phoneTypeCode": 4
      }
    ]
  },
  "postalAddress": {
    "as": {
      "postalCode": "Z2P 0B1",
      "streetName": "134 Some Street SW",
      "unitNumber": "",
      "provinceCode": 20,
      "streetNumber": "1144"
    },
    "city": "Somecity",
    "typeCode": 1
  },
  "partnerTypeCode": 4
}

I am trying to query with Sequelize on the email address (partner_email = '[email protected]') - I have tried:

where: {
  partner_json: {
    communication: {
      $contains: {
        email: [{
          value: partner_email
        }]
      }
    }
  }
}, 

as per https://github.com/sequelize/sequelize/issues/5173 and

where: {
  partner_json: {
    communication: {
      email: {
        $contains: [{
          value: partner_email
        }]
      }
    }
  }
}, 

As shown at https://github.com/sequelize/sequelize/issues/7349

But both options throw the same error (with the path slightly different based on the query):

Unhandled rejection Error: Invalid value { email: [ { value: '[email protected]' } ] }
at Object.escape (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\sql-string.js:66:11)
at Object.escape (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:934:22)
at Object._whereParseSingleValueObject (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2429:41)
at Object.whereItemQuery (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2131:21)
at Utils.getOperators.forEach.op (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2265:25)
at Array.forEach (<anonymous>)
at Object._traverseJSON (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2263:32)
at _.forOwn (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2243:12)
at F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\lodash\lodash.js:4925:15
at baseForOwn (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\lodash\lodash.js:3010:24)
at Function.forOwn (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\lodash\lodash.js:13013:24)
at Object._whereJSON (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2242:7)
at Object.whereItemQuery (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2119:19)
at Utils.getComplexKeys.forEach.prop (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1994:25)
at Array.forEach (<anonymous>)
at Object.whereItemsQuery (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1992:35)
at Object.getWhereConditions (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2456:19)
at Object.selectQuery (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1140:28)
at QueryInterface.select (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\query-interface.js:1105:27)
at Promise.try.then.then.then (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\sequelize\lib\model.js:1604:34)
at tryCatcher (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\bluebird\js\release\util.js:16:23)
at Promise._settlePromiseFromHandler (F:\Binoids\SYML\GIT\syml-sequelize-sps\node_modules\bluebird\js\release\promise.js:512:31)

Sequelize.Op is declared earlier in my code as:

const Sequelize = require('sequelize');
const Op = Sequelize.Op;
const operatorsAliases = {
    $contains: Op.contains
}

This is my first attempt at using Op.contains. I've successfully used Op.eq with a similar structure (though not into an array) elsewhere. I did try another field in the array to be sure it wasn't something to do with the email address, but same issue.

The Postgres query:

SELECT * FROM partner where partner_json->'communication'->'email' @> [{"value": "[email protected]"}]'

works correctly and retrieves the expected result.

I am using the latest version of Sequelize and PG.

I'm not sure what I'm doing wrong as both are reported to work. Anyone see where I'm going wrong?

Answer 1

I encountered the same error when trying to determine how to use OR. It turned out the issue was with how I was calling OR, not with the actual value, even though the error message stated "Invalid value". This worked for me:

where: {
            [Sequelize.Op.or]: [{book: true}, {hardcover: true}]
        },

I used this page as a reference: Sequelize API Reference

I believe aliases are now deprecated, so perhaps this will work:

where: {
    partner_json: {
        communication: {
            email: {
                [Sequelize.Op.contains]: [{
                    value: partner_email
                }]
            }
        }
    }
},