Sequelize ORM query on JsonB array in PostgreSQL

Question

How can I use some matching conditions and logical operations(lt, et, gte etc.) on Jsonb array of objects ([{...}, {...}]) in PostgreSQL using Sequelize ORM.

TableName: calls

id	direction	metaData
1	inbound	[{...}, {...}]
2	outbound	[{...}, {...}]

metaData:

[{
    "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",
}]

I want to search for data from the table base on the metaData conditions using Sequelize ORM.

Example 1: fetch all rows where agentName='Ext 204' AND duration >= 136

Example 2: fetch all rows where agentName='Ext 204' AND startedAt >= '2020-08-31 10:07:00'

My model query:

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

The above model search call executes following query:

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

My Attempt: which is not working

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

Resultant query:

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

Required: Unable to add some more conditions as duration < 140

Answer 1

Since you are querying against the JSONB field, the regular logical operations won't work.

For instance, you used Op.contains in your example and this is composed as "calls"."metaData" @> ... which @> is a special operator for JSON field.

You can find more JSON operators here: https://www.postgresql.org/docs/12/functions-json.html

And for greater than operation, you can use @@ operator to check elements within the array. As far as I know, Sequelize do not have support for this @@ operations, I use literal SQL.

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

This will return the entire records (including all elements in metaData) if any elements within the metaData matches the condition.