Search code examples
sqlpostgresqljsonb

How to return only subset of elements from a JSONB field in postgresql


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":{}}]

Solution

  • 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"}]