sql - PostgreSql : Json Array to Rows using Lateral Join

Question

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.

{
    "city": "London",
    "name": "Sainburry",
    "quantities": [112, 145, 222, 122, 124],
    "prices": [4, 4, 4, 0, 3],
    "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
}

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

select quantities,
       prices,
       AVG(quantities/prices::float) as ratio
from my_table
where city = 'London'
group by quantities, prices;

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

select q.*
from my_table mt
  cross join json_array_elements_text(details -> 'quantities') as q

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.

Answer 1

Is this what you want ?

    -- just simulate table:
with my_table(details) as(
values
('{
"city": "London",
"name": "Sainburry",
"quantities": [112, 145, 222, 122, 124],
"prices": [4, 4, 4, 0, 3],
"dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
}'::json)
)


-- here is query:
select  
my_table.details->>'city',  u.quantities, u.prices  
from my_table
JOIN LATERAL UNNEST( 
    ARRAY(SELECT json_array_elements_text(details->'quantities')) ,
    ARRAY(SELECT json_array_elements_text(details->'prices')) 
) u(quantities, prices) ON TRUE
WHERE
my_table.details->>'city' = 'London'

See demo