Query in JSONB array of Postgres database

Question

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

score 1 · Accepted Answer · answered Jan 13 '19 at 15:40

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

Thanks for the detailed info, it worked like a CHAMP :) – Sekhar Dutta Jan 15 '19 at 06:42 — Sekhar Dutta, Jan 15 '19 at 06:42

Query in JSONB array of Postgres database

1 Answers1