Question 89651 in PostgreSQL Question posted in
The official documentation can be found here.

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

2

Answers


  1. 0

    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"}]
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search