How to get only matching json object from JSONB array column using where clause

[nikhilenr]

Discussed in #16829

^{Originally posted by nikhilenr December 2, 2023}
When sequelize findAndCountAll method is used to query JSONB column type that has jsonarray content, sequelize returns whole jsonArray which has non-matching filter criteria.

e.g. Table ABC has column C(jsonb type). Column C has below json array as contents.
[ {"id": 1, "name": "test1"},
{"id": 2, "name": "test2"}
]

When i do search for name= test2 using findAndCountAll(where:
C: {
[Op.contains]: [{
name: 'test12'
}]
}
)
, sequelize returning whole json array i.e. both names json objects.

My objective is to get only subset of jsonarray which matches the filter criteria.

e.g. Getting currently this:-
[ {"id": 1, "name": "test1"},
{"id": 2, "name": "test2"}
]

My need is
[ {"id": 2, "name": "test2"}
]