PostgreSql：使用横向联接的行的Json数组

PostgreSql：使用横向联接的行的Json数组

似乎由于数组的顺序，您需要

WITH ORDINALITY

连同

LEFT JOIN LATERAL

s来匹配数组的相应元素：

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;

[Demo](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f1d10e0675b6342288775522462d13c5)