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.
Advertisement
Answer
Seems you need WITH ORDINALITY
along with LEFT JOIN LATERAL
s to match the corresponding elements of the arrays due to the order in the arrays, respectively :
SELECT q.elm AS quantities, p.elm AS prices, AVG(p.elm::float/q.elm::float) AS ratio FROM my_table t0 LEFT JOIN LATERAL jsonb_array_elements(details -> 'quantities') WITH ORDINALITY AS q(elm, i) ON TRUE LEFT JOIN LATERAL jsonb_array_elements(details -> 'prices') WITH ORDINALITY AS p(elm, i) ON q.i = p.i LEFT JOIN LATERAL jsonb_array_elements(details -> 'dates') WITH ORDINALITY AS d(elm, i) ON d.i = q.i WHERE t0.details ->> 'city' = 'London' GROUP BY q.elm, p.elm;