My question is how can I write a query for the conditional count. I have an array of objects like this on my Postgres table which is jsonb
"instances": [
{
"type": "bbox",
},
{
"type": "bbox",
},
{
"type": "bbox",
},
{
"type": "ellipse"
}
]
I have to find count of rows for which, for example, in the instances array there are more than two objects which type is equal to boxx, but with sequelize operators
sql solution
"I need to write some query like this to find rows with bbox or ellipse"
SELECT *
FROM your_table AS t
WHERE jsonb_path_exist(t.your_json_column :: jsonb, '$.**.keyvalue() ? (exists(@.key == 'bbox' || @.key == 'ellipse'))')
"I have to find count of rows for which, for example, in the instances array there are more than two objects which type is equal to bbox"
SELECT count(*)
FROM your_table AS t
CROSS JOIN LATERAL
( SELECT count(*) AS count
FROM jsonb_path_query(t.your_json_column :: jsonb, '$[*] ? (@.type == "bbox")')
) AS c
WHERE c.count > 2