postgresql Looping through JSONB array and performing SELECTs

Question

I have jsonb in one of my table

the jsonb looks like this

my_data : [
     {pid: 1, stock: 500},   
     {pid: 2, stock: 1000},
     ...
]

pid refers to products' table id ( which is pid )

EDIT: The table products has following properties: pid (PK), name

I want to loop over my_data[] in my JSONB and fetch pid's name from product table.

I need the result to look something like this (including the product names from the second table) ->

my_data : [ 
       { 
          product_name : "abc", 
          pid: 1, 
          stock : 500 
       },
       ...
]

How should I go about performing such jsonb inner join?

Edit :- tried S-Man's solutions and i'm getting this error

"invalid reference to FROM-clause entry for table \"jc\""

here is the SQL QUERY

Your question states that you have jsonb value and you want a jsonb output. But fetching pid and product... It is quite confusing. What are you expecting? — S-Man
– S-Man, Commented Aug 15, 2019 at 11:45
@S-Man I have a main table which as a jsonb property. That jsonb property is my_data ( array ). This array of object has pid which references to another table name 'Products'. So i need to perform a query which can connect pid with name and give me the desired output. The query in question is executed on the main table — Élisa Plessis
– Élisa Plessis, Commented Aug 15, 2019 at 11:47

S-Man · Accepted Answer · 2019-08-15 12:10:00Z

1

step-by-step demo:db<>fiddle

SELECT
    jsonb_build_object(                                                            -- 5
        'my_data',
        jsonb_agg(                                                                 -- 4
             elems || jsonb_build_object('product_name', mot.product_name)         -- 3
        )
    )
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems                              -- 1
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid                          -- 2

Expand JSON array into one row per array element
Join the other table against the current one using the pid values (notice the ::int cast, because otherwise it would be text value)
The new columns from the second table now can be converted into a JSON object. This one can be concatenate onto the original one using the || operator
After that recreating the array from the array elements again
Putting this in array into a my_data element

Another way is using jsonb_set() instead of step 5 do reset the array into the original array directly:

step-by-step demo:db<>fiddle

SELECT
   jsonb_set(
       mydata,
       '{my_data}',
       jsonb_agg(
            elems || jsonb_build_object('product_name', mot.product_name)
       )
   )
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid
GROUP BY mydata

edited Aug 15, 2019 at 12:10

answered Aug 15, 2019 at 11:49

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

20 Comments

Élisa Plessis Over a year ago

my jsonb array conntains only the pid which is the foreign key to products table pid. So i need to perform a join query in order to gain the product_name column

S-Man Over a year ago

Changed, added step-by-step demo fiddle

S-Man Over a year ago

Added a second way

Élisa Plessis Over a year ago

WOW ty for your quick reply. Really appreciated. Also for second case , if i had multiple nested array. For example :- [ { heading: 'test', pItems: [{ pid: 5,stock 500 }] } ] How would you unnest multiple arrays ?

S-Man Over a year ago

If you have only one element at the top the first case, otherwise you have to expand two array with the same function (second case): dbfiddle.uk/…

|

Collectives™ on Stack Overflow

postgresql Looping through JSONB array and performing SELECTs

1 Answer 1

20 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Related