node.js - Sequelize Op.contains 抛出未处理的拒绝错误 : Invalid value

Question

给定 Postgres 10 JSONB 数据字段 partner_json:

{
  "guid": "a659883cedf44131a700a6f563f2c484",
  "name": "Testing",
  "referrerId": 1,
  "communication": {
    "email": [
      {
        "value": "email@address.com",
        "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
}

我正在尝试使用 Sequelize 查询电子邮件地址 (partner_email = 'email@address.com') - 我已经尝试过:

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

根据 https://github.com/sequelize/sequelize/issues/5173 和

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

如https://github.com/sequelize/sequelize/issues/7349所示

但是这两个选项都会抛出相同的错误(路径根据查询略有不同):

Unhandled rejection Error: Invalid value { email: [ { value: 'email@address.com' } ] }
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 在我的代码前面声明为:

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

这是我第一次尝试使用 Op.contains。我已经在其他地方成功地使用了具有类似结构(尽管不是数组)的 Op.eq。我确实尝试了数组中的另一个字段以确保它与电子邮件地址无关，但同样的问题。

Postgres 查询:

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

工作正常并检索到预期结果。

我正在使用最新版本的 Sequelize 和 PG。

我不确定我做错了什么，因为据报道两者都有效。有人看到我哪里出错了吗？

Answer 1

我在尝试确定如何使用 OR 时遇到了同样的错误。事实证明，问题出在我调用 OR 的方式上，而不是实际值上，即使错误消息显示“无效值”。这对我有用:

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

我使用此页面作为引用: Sequelize API Reference

我相信别名现在已被弃用，所以也许这会起作用:

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