how to join on the values returned from Json_array_elements in postgresql?

Question

Structure of "Order" table:

objectId text, 
subOrders jsonb

structure of Jsonb: array of jsonb objects. eg.

[
   {
      "className":"SubOrder",
      "__type":"Pointer",
      "objectId":"tIENf1PCHO"
   },
   {
      "className":"SubOrder",
      "__type":"Pointer",
      "objectId":"yKVDFEuWx7"
   }
]

Structure of "SubOrder" table:

objectId text,
orderitems jsonb

structure of suborder.orderitems jsonb, eg:

[
   {
      "className":"SubOrderItem",
      "__type":"Pointer",
      "objectId":"6d1DLrmOTD"
   },
   {
      "className":"SubOrderItem",
      "__type":"Pointer",
      "objectId":"xK6mR2PDDU"
   }
]

I need to join Order.suborders.objectId with suborder.objectid

AS Order.suborders column is a jsonb array of objects, I am taking the array elements as below.

select jsonb_array_elements("subOrders")->>'objectId' 
from "Order"

Then is the problem to join the returned objectId with the Suborder table.

@ a_horse_with_no_name thanks for editing the question. Please help. — DDD
– DDD, Commented Apr 11, 2018 at 8:32

score 4 · Accepted Answer · 2018-04-11 08:30:49Z

4

Put the function call into the FROM clause and de-reference the actual field of the JSON object later.

select o.object_id, so.*
from "Order" o
  cross join lateral jsonb_array_elements(sub_orders) j(suborder)
  join sub_order so on so.object_id = j.suborder ->> 'objectId'

Online example: http://rextester.com/GQBF88668

edited Apr 11, 2018 at 8:30

answered Apr 11, 2018 at 5:50

user330315

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

6 Comments

DDD Over a year ago

my query is like thisselect o.*, j.*, ot.* from "Order" o cross join lateral select jsonb_array_elements("subOrders")->>'objectId' as j(object_id) from "Order" join "SubOrder" ot on ot."objectId" = j.object_id; and my error is syntax error at or near "->>" LINE 3: ...oss join lateral jsonb_array_elements("subOrders")->>'object... Please help

DDD Over a year ago

all my objectid are text fields only.

DDD Over a year ago

select o.*, j.*, ot.* from "Order" o cross join lateral jsonb_array_elements(o."subOrders")->>'objectId' as j("object_id") join "SubOrder" ot on ot."objectId" = j."object_id";... sorry that one is not correct. even with this there is same error.'

DDD Over a year ago

Edited my question with the structure of the tables involved and the requirement. Thanks.

DDD Over a year ago

Done. Thank a lot.

|

M Ashraful A · Accepted Answer · 2018-04-11 05:27:48Z

2

I would use WITH Queries

WITH suborders_object AS(
select jsonb_array_elements("subOrders")->>'objectId' AS obid from Order
)
SELECT * FROM suborders_object JOIN Order ON (suborders_object.obid = Order.id);

or any way you like to join suborders_object as like a table.

answered Apr 11, 2018 at 5:27

M Ashraful A

6377 silver badges13 bronze badges

2 Comments

DDD Over a year ago

Thanks much Ashraful. can you also share the other method like joining with other table pls?

user5871859 Over a year ago

What about PostgreSql : Json Array to Rows using Lateral Join?

Collectives™ on Stack Overflow

how to join on the values returned from Json_array_elements in postgresql?

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related