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?

Rijeka

Was you able to resolve this? – Woodchuck

@Rijeka how did you solve this problem? – Ponzo

Answer 1

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

Claud

Answer 2

How about like this ?

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

Depart

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Cusack

Answer 3

@> operator only works for comparing JSONB objects to JSONB arrays, like in this case, {"TeacherId":"1"} is being compared to whole JSONB array. So, in order to get {TeacherId":"1"} from JSONB object, you need to use -> operator. Here is the code snippet:

WHERE "MyTable"."MyField" -> 'TeacherId' = '1';

Hope this helps

Tipple