Below JSON is one of the column of type JSONB in my table 'logic', I want to query to check how many rows are there with type: QUESTION (any entry within conditions).
{
"name": null,
"conditions": [
{
"type": "QUESTION",
"question": {
}
},
{
"type": "QUESTION",
"question": {
}
},
{
"type": "FIELD",
"question": {
}
}
],
"expression": "A"
}
If you want to check the number of times "type": "QUESTION" entry appears within conditions of the jsonb column throughout the table.
select count(*) FROM logic CROSS JOIN LATERAL
jsonb_array_elements(jsonb_col->'conditions')as j(typ)
WHERE j->>'type' = 'QUESTION'
If you want to check the number of times "type": "QUESTION" entry appears within conditions for each row.
select jsonb_col,count(*) FROM logic CROSS JOIN LATERAL
jsonb_array_elements(jsonb_col->'conditions')as j(typ)
WHERE j->>'type' = 'QUESTION'
group by jsonb_col
If you want to check how many rows have at least one entry within conditions with
'type' = 'QUESTION',
select count(*) FROM
(
select DISTINCT jsonb_col FROM logic CROSS JOIN LATERAL
jsonb_array_elements(jsonb_col->'conditions')as j(typ)
WHERE j->>'type' = 'QUESTION'
)s;
Use the query which you find is appropriate for you
