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

一尘不染

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

json

我的表的详细信息字段中有两个以下JSON数组，并且需要像在另一个关系表中使用的那样评估查询。

{
    "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数组评估以下查询：

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

我使用了以下查询 和许多类似的查询，包括横向联接 ：

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

但是，当通过交叉联接将其他字段（价格和日期）添加到查询中时，行数成倍增加。因此，我正在寻找要使用的新功能Lateral Join，但无法正确应用。如何Lateral Join在PostgreSQL中使用获得先前查询的结果？任何帮助，将不胜感激。

更新：

这是小提琴。如果我成功地将json数组值转换为行而不乘（可以返回5条记录），则可以评估所需的结果。只需帮助我使用
横向连接和 json_array_elements_text 将json数组转换为行。

阅读 226

2020-07-27

共1个答案

一尘不染

似乎由于数组的顺序，您需要WITH ORDINALITY连同LEFT JOIN LATERALs来匹配数组的相应元素：

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)

2020-07-27