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

Could you clarify your example? Do you mean "...rows where any object within metaData match the conditions" or "fetch rows where all objects in metaData matches"? — Emma
– Emma, Commented Oct 11, 2022 at 18:53
@Emma: Thanks for taking interest in this problem. Psuedo Code: SELECT * FROM calls WHERE calls.direction = 'outbound' and calls.metaData.duration > 136; — Zia
– Zia, Commented Oct 14, 2022 at 13:17
in your example metaData, duration > 136 only matches for metaData.id=2 and not for metaData.id=1, in this case do you want this record (id=2, direction=outbound) to return? — Emma
– Emma, Commented Oct 14, 2022 at 13:26
Yes but I'm unable to add logical operations (>, <, <= etc) in JsonB field on the duration — Zia
– Zia, Commented Oct 14, 2022 at 13:39

Emma · Accepted Answer · 2022-10-14 14:33:55Z

3

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.

answered Oct 14, 2022 at 14:33

Emma

9,5781 gold badge22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zia Over a year ago

You're right, I also added Sequelize.literal() method to apply the condition, I didn't use logical operation yet but applied like_regix. Thanks for your answer

Collectives™ on Stack Overflow

Sequelize ORM query on JsonB array in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related