Postgres json select

Question

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

Alex Belozersky · Accepted Answer · 2020-04-23 22:14:27Z

1

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

answered Apr 23, 2020 at 22:14

Alex Belozersky

261 bronze badge

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

Collectives™ on Stack Overflow

Postgres json select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related