Postgres jsonb query on nested object

Question

My postgres db version: 9.4.4. and I have a table with this structure;

CREATE TABLE product_cust
(
 productid character(2),
  data jsonb,
)

I have records like this in the "data" column;

{"productid":"01","cust": [
        {"cell": ["0000xxx0", "0000xxx1"], "name": "John", "email": ["[email protected]"], "custtype": "c"}, 
        {"cell": ["0000xxx2", "0000xxx3"], "name": "Smith", "email": ["[email protected]"], "custtype": "c"}  
]}

I need to extract all records for "cell" . Expected record will be

["0000xxx0", "0000xxx1","0000xxx2", "0000xxx3"]

or for "email" ["[email protected]","[email protected]"]

My best effort below has been a two(2) step process and will not scale for x no of "cust" objects;

select (data::json#>'{cust,0}')::json#>'{cell}' from product_cust; //return "0000xxx0", "0000xxx1"
select (data::json#>'{cust,1}')::json#>'{cell}' from product_cust; //return "0000xxx2", "0000xxx3"

I will be most grateful if i can be pointed in the right direction

klin · Accepted Answer · 2015-09-14 18:38:37Z

4

Use json_agg() and jsonb_array_elements() functions:

select json_agg(cell)
from (
    select jsonb_array_elements(elem->'cell') cell
    from (
        select jsonb_array_elements(data->'cust') elem
        from product_cust
        ) subsub
    ) sub

You can merge two inner subqueries:

select json_agg(cell)
from (
    select jsonb_array_elements(jsonb_array_elements(data->'cust')->'cell') cell
    from product_cust
    ) sub

Group results by productid:

select productid, json_agg(cell)
from (
    select productid, jsonb_array_elements(jsonb_array_elements(data->'cust')->'cell') cell
    from product_cust
    ) sub
group by 1
order by 1

edited Sep 14, 2015 at 18:38

answered Sep 14, 2015 at 18:28

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BlowMan Over a year ago

real grateful, you have definitely pointed me there, will read on those links. Can you update your query to group by productid, so that for each "productid",there are the associated "cell"

BlowMan Over a year ago

Wow, superfast, super klin!!

Collectives™ on Stack Overflow

Postgres jsonb query on nested object

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related