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 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

S-Man S-Man · Accepted Answer · 2019-08-15T11:49:28

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

postgresql Looping through JSONB array and performing SELECTs

1 Answers