0

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":{}}]
Daniel
  • 341
  • 6
  • 24

2 Answers2

2

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"}]
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • thank you, this looks good, One issue I can have is if I want to see (@name=="b") || (@name=="c"). I would need to make a group by id, right? I forgot to add this case in my original question. – Daniel Feb 23 '23 at 17:16
  • See **UPDATE** for new query and if that is what you want. – Adrian Klaver Feb 23 '23 at 17:44
1

Extract elements, filter, aggregate

select id, name, jsonb_agg(e) objects
from (
   select id, jsonb_array_elements(objects) e
   from mytable ) t
where e::jsonb ->> 'name' = 'b'
group by id, name
Serg
  • 22,285
  • 5
  • 21
  • 48
  • This works, is there a way to do it just from the select zone? Since i am just changing only a field's value. – Daniel Feb 23 '23 at 17:25