javascript - 查询 JSONB。Sequelize Op.In 抛出错误：无效值

Question

我有一个名为“config”的 postgresql JSONB 字段。

“config”字段中的值示例

{ 
   "rewards":{ 
      "policyA":[ 
         { 
            "enforcedDate":"some-date",
            "anotherKey":"some-val"
         },
         { 
            "enforcedDate":"some-date-2",
            "anotherKey":"some-val-2"
         }
      ]
   }
}

这是我尝试过的。我正在尝试查找所有具有“某个日期”的强制日期的记录。

MyEntity.findAndCountAll({
  where: {
    config: {
      rewards: {
        policyA: [ { enforcedDate: "some-date" } ]
      }
    }
  }
});

或者

MyEntity.findAndCountAll({
  where: {
    config: {
      rewards: {
        policyA: {
          [Op.in]: [ { enforcedDate: "some-date" } ]
        }
      }
    }
  }
});

我得到的错误

Invalid value { enforcedDate: 'some-date' }

      at escape (node_modules/sequelize/lib/sql-string.js:65:11)
      at Object.partialEscape [as escape] (node_modules/sequelize/lib/sql-string.js:57:37)
      at ABSTRACT.<anonymous> (node_modules/sequelize/lib/dialects/postgres/data-types.js:469:22)
          at Array.map (<anonymous>)
      at ABSTRACT._value (node_modules/sequelize/lib/dialects/postgres/data-types.js:458:19)
      at ABSTRACT._stringify (node_modules/sequelize/lib/dialects/postgres/data-types.js:473:29)
      at ABSTRACT.stringify (node_modules/sequelize/lib/data-types.js:23:19)
      at Object.escape (node_modules/sequelize/lib/sql-string.js:59:40)
      at PostgresQueryGenerator.escape (node_modules/sequelize/lib/dialects/abstract/query-generator.js:986:22)
      at PostgresQueryGenerator._whereParseSingleValueObject (node_modules/sequelize/lib/dialects/abstract/query-generator.js:2556:41)
      at PostgresQueryGenerator.whereItemQuery (node_modules/sequelize/lib/dialects/abstract/query-generator.js:2272:21)
      at PostgresQueryGenerator._traverseJSON (node_modules/sequelize/lib/dialects/abstract/query-generator.js:2392:21)
      at node_modules/sequelize/lib/dialects/abstract/query-generator.js:2385:14
      at node_modules/lodash/lodash.js:4905:15
      at baseForOwn (node_modules/lodash/lodash.js:2990:24)
      at Function.forOwn (node_modules/lodash/lodash.js:13014:24)
      at PostgresQueryGenerator._traverseJSON (node_modules/sequelize/lib/dialects/abstract/query-generator.js:2384:9)
      at node_modules/sequelize/lib/dialects/abstract/query-generator.js:2361:12
      at node_modules/lodash/lodash.js:4905:15
      at baseForOwn (node_modules/lodash/lodash.js:2990:24)
      at Function.forOwn (node_modules/lodash/lodash.js:13014:24)
      at PostgresQueryGenerator._whereJSON (node_modules/sequelize/lib/dialects/abstract/query-generator.js:2360:7)

似乎类似于Sequelize Op.contains throws Unhandled rejection Error: Invalid value

这没有答案。

像下面的 1 这样的原始 sql 查询工作正常

 select *
    FROM "MyEntity"
    where config @> '{"rewards": {"policyA": [{"enforcedDate": "some-date"}]}}';

标签： javascriptnode.jspostgresqlsequelize.js

Answer 1

您应该使用Op.contains，例如

index.ts：

import { sequelize } from '../../db';
import { Model, DataTypes, Op } from 'sequelize';
import assert from 'assert';

class MyEntity extends Model {}
MyEntity.init(
  {
    config: DataTypes.JSONB,
  },
  { sequelize, modelName: 'MyEntity' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await MyEntity.create({
      config: {
        rewards: {
          policyA: [
            { enforcedDate: 'some-date', anotherKey: 'some-val' },
            { enforcedDate: 'some-date-2', anotherKey: 'some-val-2' },
          ],
        },
      },
    });

    const rval = await MyEntity.findAndCountAll({
      where: {
        config: {
          [Op.contains]: {
            rewards: {
              policyA: [{ enforcedDate: 'some-date' }],
            },
          },
        },
      },
    });
    assert(rval.count === 1, 'The count of entities should be equal 1');
    console.log('entity.config:', rval.rows[0]['config']);
    const rval2 = await MyEntity.findAndCountAll({
      where: {
        config: {
          [Op.contains]: {
            rewards: {
              policyA: [{ enforcedDate: 'some' }],
            },
          },
        },
      },
    });
    assert(rval2.count === 0, 'The count of entities should be 0');
    console.log('rval2:', rval2);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

MyEntity数据库中表的数据行：

node-sequelize-examples=# select * from "MyEntity";
 id |                                                                      config
----+--------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {"rewards": {"policyA": [{"anotherKey": "some-val", "enforcedDate": "some-date"}, {"anotherKey": "some-val-2", "enforcedDate": "some-date-2"}]}}
(1 row)

执行代码后的结果日志：

Executing (default): DROP TABLE IF EXISTS "MyEntity" CASCADE;
Executing (default): DROP TABLE IF EXISTS "MyEntity" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "MyEntity" ("id"   SERIAL , "config" JSONB, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'MyEntity' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "MyEntity" ("id","config") VALUES (DEFAULT,$1) RETURNING *;
Executing (default): SELECT count(*) AS "count" FROM "MyEntity" AS "MyEntity" WHERE "MyEntity"."config" @> '{"rewards":{"policyA":[{"enforcedDate":"some-date"}]}}';
Executing (default): SELECT "id", "config" FROM "MyEntity" AS "MyEntity" WHERE "MyEntity"."config" @> '{"rewards":{"policyA":[{"enforcedDate":"some-date"}]}}';
entity.config: { rewards: { policyA: [ [Object], [Object] ] } }
Executing (default): SELECT count(*) AS "count" FROM "MyEntity" AS "MyEntity" WHERE "MyEntity"."config" @> '{"rewards":{"policyA":[{"enforcedDate":"some"}]}}';
Executing (default): SELECT "id", "config" FROM "MyEntity" AS "MyEntity" WHERE "MyEntity"."config" @> '{"rewards":{"policyA":[{"enforcedDate":"some"}]}}';
rval2: { count: 0, rows: [] }