CODEWITHSUNDEEP
postgresql
Zuzanna Deger
Zuzanna Deger

Reputation: 9

Postgres json select

Let's say that I have an incoming JSON message to my app:

      {
        line_nbr : 1,
        value: 10
       },   

           {
        line_nbr : 2,
        value: 30
       },   

   ]

Is it possible to perform the following selection in postgres :

SELECT  JsonObject.value, qty   from table_x  where id in  JsonObjects.line_nbr

In other words join on the incoming JSON object

Upvotes: 0

Views: 78

Answers (1)

Alex Belozersky
Alex Belozersky

Reputation: 26

with your_js as (
 select (value->>'line_nbr')::int as line_nbr
 from jsonb_array_elements('JsonObjects'::jsonb) as je
)
select line_nbr, qty
from table_x
 join your_js on line_nbr = table_x.id

check here for detail

Upvotes: 1

Related Questions