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