CODEWITHSUNDEEP
postgresqljson-query
Sekhar Dutta
Sekhar Dutta

Reputation: 109

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

Upvotes: 0

Views: 342

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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

Upvotes: 1

Related Questions