Sequelize Op.contains 抛出未处理的拒绝错误:无效值 [英] Sequelize Op.contains throws Unhandled rejection Error: Invalid value

问题描述

鉴于 Postgres 10 JSONB 数据字段 partner_json:

Given the Postgres 10 JSONB data field partner_json:

{
  "guid": "a659883cedf44131a700a6f563f2c484",
  "name": "Testing",
  "referrerId": 1,
  "communication": {
    "email": [
      {
        "value": "[e-mail]",
        "primary": true,
        "emailTypeCode": 2
      }
    ],
    "phone": [
      {
        "primary": true,
        "phoneNumber": "[phone]",
        "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 = '[e-mail]') - 我已经尝试过:

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

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

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

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

Unhandled rejection Error: Invalid value { email: [ { value: '[e-mail]' } ] }
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 之前在我的代码中声明为:

Sequelize.Op is declared earlier in my code as:

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

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

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.

Postgres 查询:

The Postgres query:

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

正常工作并检索预期结果.

works correctly and retrieves the expected result.

我使用的是最新版本的 Sequelize 和 PG.

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?

推荐答案

我在尝试确定如何使用 OR 时遇到了同样的错误.结果证明问题在于我如何调用 OR，而不是实际值，即使错误消息指出无效值".这对我有用:

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}]
        },

我使用这个页面作为参考:Sequelize API 参考

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
                }]
            }
        }
    }
},

这篇关于Sequelize Op.contains 抛出未处理的拒绝错误:无效值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！