Sequelize find in JSON array field Postgresql

Question

I have PostgreSQL database with JSONB type field named "metadata" that has following content structure:

row1: {   
"name": "test",   
"description": "test",   
"attributes" : [      {        "type": "level" ,       "level": 1      },      {        "type": "name"    ,    "name": "Fish"      }   ] 
}

row2: {   
"name": "test2",   
"description": "test2",   
"attributes" : [      {        "type": "level",        "level": 4      },      {        "type": "name"  ,      "name": "Cat"      }   ] 
}

row3: {   
"name": "test4",   
"description": "test4",   
"attributes" : [      {        "type": "level",        "level": 7      },      {        "type": "name"    ,    "name": "Dog"      }   ] }

I try to make findAll query request with filter by attributes.level between [1, 5] using Sequelize, but don't get some results. how find between json array?

Answer 1

Here it is as a parameterized query. :lo and :hi are parameters. the_table CTE stands for the real table as an illustration.

with the_table(r, metadata) as 
(
 values
 ('row1', '{"name": "test",  "description": "test",  "attributes" : [ { "type": "level" , "level": 1}, { "type": "name" , "name": "Fish" }]}'::jsonb),
 ('row2', '{"name": "test2", "description": "test2", "attributes" : [ { "type": "level", "level": 4 }, { "type": "name" , "name": "Cat" }]}'),
 ('row3', '{"name": "test4", "description": "test4", "attributes" : [ { "type": "level", "level": 7 }, { "type": "name" , "name": "Dog" }]}')
)
select * from the_table
where exists 
(
  select from jsonb_array_elements(metadata -> 'attributes') e 
  where (e ->> 'level')::numeric between :lo and :hi
);