I have a table that has a jsonb column which has an array of objects.

Looks like this for every row.

[{grade: 'A', subject: 'MATH'}, {grade: 'B', subject: 'PHY'}...]

Now querying over it thanks to this post https://stackoverflow.com/a/30592076/2405689 .

Problem comes it takes at least 2.4 secs to finish querying a count of all students with grade IN (A, B, C).

I would like to get some help on indexing this because, the indexes I have tried didn't do anything.

DROP INDEX idx_subjects_subject;
DROP INDEX idx_subjects_grade;
CREATE INDEX idx_subjects_subject ON results USING GIN((subjects-> 'subject'));
CREATE INDEX idx_subjects_grade ON results USING GIN((subjects-> 'grade'));

Also did (separately):

DROP INDEX idx_subjects_standard;
CREATE INDEX idx_subjects_standard ON results USING GIN(subjects);

I'm querying them like this.

SELECT COUNT(*)
FROM results
WHERE EXISTS 
    (
        SELECT 1 
        FROM jsonb_array_elements(subjects) AS j(data) 
        WHERE (data #>> '{subject}') LIKE '%MATH%' 
        AND 
        (data #>> '{grade}') IN ('A', 'B', 'C')
    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

I also tried querying it like this :

SELECT COUNT(*)
FROM results
WHERE EXISTS 
    (
    SELECT 1 
    FROM jsonb_array_elements(subjects) AS j(data) 
    WHERE data @> '{"subject": "B/MATH", "grade": "A"}'
    OR data @> '{"subject": "B/MATH", "grade": "B"}'
    OR data @> '{"subject": "B/MATH", "grade": "C"}'

    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

But this gave me a reversed effect (3 secs query).

Here is my explain analyze block.

"Aggregate  (cost=1888617.19..1888617.20 rows=1 width=8) (actual time=2517.116..2517.117 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on results  (cost=470767.18..1888021.96 rows=238090 width=0) (actual time=680.456..2514.633 rows=24002 loops=1)"
"        Recheck Cond: ("examYear" = '2010'::text)"
"        Rows Removed by Index Recheck: 557945"
"        Filter: (("examType" = 'CSEE'::text) AND (SubPlan 1))"
"        Rows Removed by Filter: 500054"
"        Heap Blocks: exact=40156 lossy=53452"
"        ->  Bitmap Index Scan on idx_results_subjects  (cost=0.00..470707.66 rows=528405 width=0) (actual time=672.375..672.375 rows=524056 loops=1)"
"              Index Cond: ("examYear" = '2010'::text)"
"        SubPlan 1"
"          ->  Function Scan on jsonb_array_elements j  (cost=0.00..2.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=458487)"
"                Filter: (((data #>> '{subject}'::text[]) ~~ '%MATH%'::text) AND ((data #>> '{grade}'::text[]) = ANY ('{A,B,C}'::text[])))"
"                Rows Removed by Filter: 8"
"Planning time: 0.126 ms"
"Execution time: 2517.145 ms"

I'm on postgresql: 9.6.1