Query based on JSON object values-CodePudding

If I have a column(lets call it students) in a PostgreSQL db in a JSON/JSONB format like such:

"1":{ "student_id":"1","student_name":"Tim","Pass":true}

"2":{ "student_id":"2","student_name":"Jim","Pass":true}

"3":{ "student_id":"3","student_name":"Pam","Pass":false}

How can I query the students who have passed?

Ideally, I'd write something like SELECT (students->'student_id') FROM table_name WHERE (students->'Pass')=true, but that doesn't work because it has the missing layer of keys i.e "1", "2" and "3". Something that has its logic similar to ...WHERE (students-> *All Keys* ->'Pass')=true is what I'm looking for.

CodePudding user response：

Sample data

CREATE TABLE test5 (
    jsondata jsonb NULL
);
INSERT INTO test5 (jsondata) VALUES('{"1": {"Pass": true, "student_id": "1", "student_name": "Tim"}}'::jsonb);
INSERT INTO test5 (jsondata) VALUES('{"2": {"Pass": true, "student_id": "2", "student_name": "Jim"}}'::jsonb);
INSERT INTO test5 (jsondata) VALUES('{"3": {"Pass": false, "student_id": "3", "student_name": "Pam"}}'::jsonb);

Variant 1 (using extract key value)

select t2.* from test5 t1
cross join jsonb_each_text(t1.jsondata) as t2
where ((t2."value"::jsonb)->'Pass') = 'true'

Variant 2 (using SQL/JSON)

select * from test5 t1
where jsonb_path_match(t1.jsondata, '$.**.Pass == true')