Query based on JSON object values

Question

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.

What is the "1" in the json, is it part of the json itself? { "1" : { .... } } ? If you remove it the jsonpath you need will be simplified. Otherwise the jsonpath will be more complex. I think using the $.[*]. jsonpath is possible. — SimplyCode
– SimplyCode, Commented Oct 2, 2022 at 11:13

Ramin Faracov · Accepted Answer · 2022-10-02 11:50:46Z

1

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')

answered Oct 2, 2022 at 11:50

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Query based on JSON object values

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