Skip to content
Advertisement

PostgreSql : Json Array to Rows using Lateral Join

I have two following JSON Array in details field of my table and need to evaluate the query as I use in another relational table.

I want to evaluate the following query for this JSON Array:

I used the following query and many similar queries including lateral join:

But, when adding the other fields (prices and dates) to the query by cross join, the rows multiplied. So, I am looking for a new feature Lateral Join to use, but not able to apply properly. How can I obtain the result I obtained previous query by using Lateral Join in PostgreSQL? Any help would be appreciated.

Update:

Here is the fiddle. I can evaluate the desired result if I succeed to convert the json array values to rows without multiplying (5 records should be returned). Just help me to convert json array to row using lateral join and json_array_elements_text.

Advertisement

Answer

Seems you need WITH ORDINALITY along with LEFT JOIN LATERALs to match the corresponding elements of the arrays due to the order in the arrays, respectively :

Demo

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement