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.

更新：

Update:

这是小提琴。如果我成功地将json数组值转换为行而不乘（可以返回5条记录），则可以评估所需的结果。只需帮助我使用 lateral join和 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.

推荐答案

似乎您需要具有顺序以及 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屋！