With
DROP TABLE IF EXISTS tst.tst;
CREATE TABLE tst.tst (id int primary key, j jsonb);
INSERT INTO tst.tst (id, j) VALUES
(1, '[{"name":"aaa","val":3,"bol":true}, {"name":"bbb","val":1,"bol":true}]'),
(2, '[{"name":"ccc","val":5,"bol":false},{"name":"bbb","val":4,"bol":false}]');
I would like to do (in pseudo code) this:
SELECT * FROM tst.tst WHERE WITHIN OBJECT ('name' = 'bbb') AND ('val' > 2);
If I have an object instead of an array I can do this:
SELECT * FROM tst.tst WHERE ('name' = 'bbb') AND ('val' > 2);
As a json newbie I researched some answers here, tried a couple of things, eg this works, but is still far away from what I want to do:
WHERE '4' IN (SELECT value->>'val' FROM jsonb_array_elements(j)) ..
Maybe I'm close ..
SELECT DISTINCT t.*
FROM tst.tst t, jsonb_array_elements(t.j) j
WHERE j.value->>'name' = 'bbb' AND (j.value->>'val')::numeric > 2;
The jsonb_array_elements() function is a set-returning-function so you should use it as a row source. In the above case it is laterally joined to your table so you can use columns from the tst.tst data. Every array element becomes a row with a single simple jsonb object which you can inspect with the standard operators. Note that the values, even for the jsonb type which stores numbers in numeric format, come out as text so you need a cast to numeric.