Query in JSONB array of Postgres database

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"
}

Solution

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

Demo