Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table logic AS
select * FROM
(
VALUES ('{
  "name": null,
  "conditions": [
    {
      "type": "QUESTION",
      "question": {
      }
    },
    {
      "type": "QUESTION",
      "question": {
      }
    },
    {
      "type": "FIELD",
      "question": {
      }
    }
  ],
  "expression": "A" }'::jsonb
  ),
 ('{
  "name": null,
  "conditions": [
    {
      "type": "QUESTION",
      "question": {
      }
    },
    {
      "type2": "QUESTION",
      "question": {
      }
    },
    {
      "type": "FIELD",
      "question": {
      }
    }
  ],
  "expression": "A"}'
  ) ,
   ('{
  "name": null,
  "conditions": [
    {
      "type3": "QUESTION",
      "question": {
      }
    },
    {
      "type2": "QUESTION",
      "question": {
      }
    },
    {
      "type": "FIELD",
      "question": {
      }
    }
  ],
  "expression": "A"}'
  )  
  ) as s(jsonb_col);
3 rows affected

select count(*) FROM logic CROSS JOIN LATERAL
 jsonb_array_elements(jsonb_col->'conditions')as j(typ) 
WHERE j->>'type'  = 'QUESTION'


count


3


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


jsonb_col
count


{"name": null, "conditions": [{"type": "QUESTION", "question": {}}, {"type2": "QUESTION", "question": {}}, {"type": "FIELD", "question": {}}], "expression": "A"}
1

{"name": null, "conditions": [{"type": "QUESTION", "question": {}}, {"type": "QUESTION", "question": {}}, {"type": "FIELD", "question": {}}], "expression": "A"}
2


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;


count


2

jsonb_col	count
{"name": null, "conditions": [{"type": "QUESTION", "question": {}}, {"type2": "QUESTION", "question": {}}, {"type": "FIELD", "question": {}}], "expression": "A"}	1
{"name": null, "conditions": [{"type": "QUESTION", "question": {}}, {"type": "QUESTION", "question": {}}, {"type": "FIELD", "question": {}}], "expression": "A"}	2