Sequelize query on JSONB array

Question

This is the JSONB array data type in the model and postgres database:

.
.
.
      MyField: {
        type: DataTypes.ARRAY(DataTypes.JSONB),
        allowNull: false
      }
.
.
.

The table field contains JSONB values:

{"{\"LessonId\": \"1\", \"TeacherId\": \"1\"}"}

And this is my where clause in sequelize findAll method:

      where: {
        MyField: {
          [Op.contains]: [
            {
              TeacherId: '1',
            }
          ]
        }
      }

The generated query is this:

SELECT
    ...
    "MyField",
FROM
    "MyTable" AS "MyTable" 
WHERE
    "MyTable"."MyField" @> ARRAY [ '{"TeacherId":"1"}' ]:: JSONB [];

And the result set is empty. But when i include LessonId too, it will do my answer. I found postgres sees the jsonb object like a String text. What is the correct way to query based on specific key in JSONB ARRAY?

Abdul Vajid Abdul Vajid · Accepted Answer · 2020-12-09T22:56:10

Your based field in ARRAY type and the operations you are trying to achieve is supported only in JSONB field type. You can simply define the model attribute as JSONB and you can still store the JSON Array inside it and run containment operations

Sequelize query on JSONB array

1 Answers