PostgreSql:使用横向连接将 Json 数组转换为行 [英] PostgreSql : Json Array to Rows using Lateral Join

问题描述

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

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"]
}

我想评估这个 JSON 数组的以下查询:

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;

我使用了以下查询以及许多类似的查询，包括横向连接:

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

但是，当通过交叉联接向查询添加其他字段(价格和日期)时，行会成倍增加.因此，我正在寻找要使用的新功能 Lateral Join，但无法正确应用.在 PostgreSQL 中如何使用 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.

更新:

这是小提琴.如果我成功地将 json 数组值转换为行而不进行乘法，我可以评估所需的结果(应返回 5 条记录).只需帮助我使用 lateral 连接和 json_array_elements_text 将 json 数组转换为行.

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.

推荐答案

似乎你需要 WITH ORDINALITY 和 LEFT JOIN LATERAL 来匹配数组的相应元素由于数组中的顺序，分别:

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 :

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;

演示

这篇关于PostgreSql:使用横向连接将 Json 数组转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！