I have a table in Postgres with the structure bellow.
id | name | objects(jsonb) |
---|---|---|
1 | first | [{"name":"a", value:"1", "param":{}}, {"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}] |
2 | second | [{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}] |
3 | third | [{"name":"c", "value":"345", "param":{}}] | |
4 | forth | [{"name":"a", "value":"123", "param":{}}, {"name":"b", "value":"456", "param":{}}] |
I am trying to write a query that returns all the rows but with only a subset of the objects in the "objects" column.
The output bellow would be the answer in case I want only the elements with "name"="b"
id | name | objects(jsonb) |
---|---|---|
1 | first | [{"name":"b", "value":"55", "param":{}}] |
2 | second | [{"name":"b", "value":"22", "param":{}}] |
3 | third | [] |
4 | forth | [{"name":"b", "value":"456", "param":{}}] |
The output bellow would be the answer in case I want only the elements with "name"="b" or "name"="c"
id | name | objects(jsonb) |
---|---|---|
1 | first | [{"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}] |
2 | second | [{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}] |
3 | third | [{"name":"c", "value":"345", "param":{}}] |
4 | forth | [{"name":"b", "value":"456", "param":{}}] |
From here JSON functions use jsonb_path_query
:
SELECT
jsonb_path_query('[{"name":"a", "value":"1", "param":{}}, {"name":"b", "value":"55", "param":{}},
{"name":"c", "value":"234", "param":{}}]'::jsonb, '$[*] ? (@.name == "b")');
jsonb_path_query
-------------------------------------------
{"name": "b", "param": {}, "value": "55"}
UPDATE Second case:
SELECT
jsonb_agg(a)
FROM
jsonb_path_query('[{"name":"a", "value":"1", "param":{}}, {"name":"b", "value":"55", "param":{}},
{"name":"c", "value":"234", "param":{}}]'::jsonb, '$[*] ? (@.name == "b" || @.name == "c")') AS t (a);
jsonb_agg
-----------------------------------------------------------------------------------------
[{"name": "b", "param": {}, "value": "55"}, {"name": "c", "param": {}, "value": "234"}]