PostgreSQL Query: Use a json field to filter entries before grouping

Question

I have created a Postgres table with the following schema,

create table exam_info
(
  "venue" text not null,    //value: "A", "B", "C", "D"
  "time" int,               //epoch time
  "examData" jsonb not null  
);

here is some example entries for examData column

{"id":"e73kf", "data":{"subject":"Science","year":"grade5"}}
{"id":"e3dsa", "data":{"subject":"English","year":"grade3"}}

I would like to find out for a specific subject(Science), how many exam has been hosted in each venue in a specific time range

subject     venue     count
"Science"     A          10
"Science"     B           5
"Science"     C           7
"Science"     D          11

When I tried to add a condition where exam = "Science" in the following query, an error column exam does not exist showed up.

SELECT
    "examData" -> 'data' -> 'subject' as exam,
    venue,
    count(venue)
FROM
    exam_info
WHERE
    time >= '   ' AND time < '    '
GROUP BY
    exam,venue

Thanks everyone!

what is "exam" column in your group by ? you don't have exam column in your table, remove it and you are ok — eshirvana
– eshirvana, Commented Apr 6, 2021 at 13:55
Your query works just fine. All the column names you selected (exam, venue) in the SELECT section are available to be used in the GROUP BY section — Bini Barazany
– Bini Barazany, Commented Apr 6, 2021 at 13:58
Note thta count(*) will be slightly faster than count(venue) — user330315
– user330315, Commented Apr 6, 2021 at 14:08
@eshirvana check the SELECT section, I have defined the json field "subject" as exam. The query itself runs fine, the error only showed up when I want to add the condition where exam = "Science" — efgdh
– efgdh, Commented Apr 6, 2021 at 14:11
Yes the query itself runs fine, the error only showed up when I want to add the condition where exam = "Science", and I would like to know how I can add this condition to the above query. — efgdh
– efgdh, Commented Apr 6, 2021 at 14:12

eshirvana · Accepted Answer · 2021-04-06 15:53:29Z

1

in comment , you are referring to another problem than your original question, anyways , you can't refer to "exam" in the where clause because you can't use column aliases in the where clause, but you can refer it the way you build the column :

SELECT
    "examData" -> 'data' -> 'subject' as exam,
    venue,
    count(venue)
FROM
    exam_info
WHERE
    time >= '   ' AND time < '    '
    and "examData" -> 'data' -> 'subject'  ='"Math"' -- <--
GROUP BY
    exam,venue

edited Apr 6, 2021 at 15:53

answered Apr 6, 2021 at 14:20

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Query: Use a json field to filter entries before grouping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related