在 PostgreSQL 中对 JsonB 数组进行 Sequelize ORM 查询

如何使用一些匹配条件和逻辑运算（lt, et, gte等）在 Jsonb 对象数组上（[{...}, {...}]）在 PostgreSQL 中使用 Sequelize ORM。

表名： calls

元数据：

[{
    "id": 1,
    "audioUrl": "https://xyz.wav",
    "duration": 136,
    "agentName": "Ext 204",
    "calledNumber": "123456789",
    "callingNumber": "987654321",
    "startedAt": "2020-08-31 5:07:00",
    "endedAt": "2020-08-31 11:07:20",
},
{
    "id": 2,
    "audioUrl": "https://abc.wav",
    "duration": 140,
    "agentName": "Ext 210",
    "calledNumber": "123456789",
    "callingNumber": "987654321",
    "startedAt": "2020-08-31 10:07:00",
    "endedAt": "2020-08-31 10:09:20",
}]

我想使用 Sequelize ORM 根据元数据条件从表中搜索数据。

示例1：获取所有行，其中agentName ='Ext 204'并且持续时间> = 136

示例2：获取所有agentName='Ext 204'并且startedAt>='2020-08-31 10:07:00'的行

我的模型查询：

const Op = Sequelize.Op;
const resp = await callModel.findAll({
      attributes: ['id', 'direction'], // table columns
      where: {
            metaData: { // jsonB column
                [Op.contains]: [
                    {agentName: 'Ext 204'},
                ],
            },
        },
    });

上述模型搜索调用执行以下查询：

SELECT "id", "direction" FROM "calls" AS "calls" WHERE "calls"."metaData" @> '[{"agentName":"Ext 205"}]';

我的尝试：哪个不起作用

callModel.findAll({
        attributes: ['id', 'direction'], // table columns
        where: {
            metaData: { // metaData
                [Op.and]: [
                    {
                        [Op.contains]: [
                            {agentName: 'Ext 204'},
                        ],
                    },
                    {
                        duration: {
                            [Op.lt]: 140
                        }
                    }
                ]
            },
        },
    });

查询结果：

SELECT "id", "direction" FROM "calls" AS "calls" WHERE ("calls"."metaData" @> '[{"agentName":"Ext 205"}]' AND CAST(("calls"."metaData"#>>'{duration}') AS DOUBLE PRECISION) < 140);

必需的：无法添加更多条件持续时间

由于您正在查询 JSONB 字段，因此常规逻辑运算将不起作用。

例如，您使用了Op.contains在你的例子中，它的组成是"calls"."metaData" @> ... which @>是 JSON 字段的特殊运算符。

您可以在此处找到更多 JSON 运算符：https://www.postgresql.org/docs/12/functions-json.html https://www.postgresql.org/docs/12/functions-json.html

对于大于操作，您可以使用@@运算符检查数组中的元素。据我所知，Sequelize 不支持这个@@操作，我用literal SQL.

where: {
  [Op.and]: [
    {
      metaData: {
        [Op.contains]: [
          {agentName: 'Ext 204'},
        ],
      }
    },
    Sequelize.literal(`"calls"."metaData" @@ '$[*].duration >= 136'`)
  ]
}

这将返回整个记录（包括元数据中的所有元素），如果any内的元素metaData符合条件。