PostgreSql : Json Array to Rows using Lateral Join

Question

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.

`group by quantities, prices` <- this part confused me. What is desirable result from that json? post please in your question — Oto Shavadze, May 10 '20 at 09:42
Here is the [fiddle](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=276a2cfc7cb591c8e189296968677252). And 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**. — , May 10 '20 at 09:47
so `quantities` and `prices` values corespondents to each other by index? and they always have same quantity of elements ? — Oto Shavadze, May 10 '20 at 09:51
quantities, prices and dates. They have always same quantity of elements and they are corresponding to each other. For example, for the first: 112, 4, "13.05.2020". ... — , May 10 '20 at 10:00
Are `quantities`, `prices` and `dates` guaranteed to have the same number of array elements at all times? Is Upgrading to a current version of Postgres an option? pg 9.5 is on its way out: https://www.postgresql.org/support/versioning/ — Erwin Brandstetter, May 11 '20 at 01:09

Oto Shavadze · Answer 1 · 2020-05-10T10:24:07.503

3

Is this what you want ?

    -- just simulate table:
with my_table(details) as(
values
('{
"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)
)


-- here is query:
select  
my_table.details->>'city',  u.quantities, u.prices  
from my_table
JOIN LATERAL UNNEST( 
    ARRAY(SELECT json_array_elements_text(details->'quantities')) ,
    ARRAY(SELECT json_array_elements_text(details->'prices')) 
) u(quantities, prices) ON TRUE
WHERE
my_table.details->>'city' = 'London'

See demo

edited May 10 '20 at 10:24

answered May 10 '20 at 10:18

Oto Shavadze

40,603
55
152
236

Very good, the result is exactly what I want. But, **1)** When I try to order via `order by my_table.details->>'quantities' asc;` It does not order the list. **2)** Should we use UNNEST and ARRAY? Thanks a lot and voted up. – May 10 '20 at 10:29
1) use: `order by u.quantities`, 2) yes, we can use it – Oto Shavadze May 10 '20 at 10:31
I fixed order problem by using `u.quantities`. What about the 2nd question? Is there a shorter version? – May 10 '20 at 10:31

Barbaros Özhan · Accepted Answer · 2020-05-10T10:33:32.157

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;

Demo

Thanks a lot for your helps, voted up. On the other hand, is there a shorter version for this problem by still using JOIN LATERAL and jsonb_array_elements? — , May 10 '20 at 10:33

Abelisto · Answer 3 · 2020-05-10T10:52:50.890

1

select
  quantity, price,
  avg(quantity/price) as ratio
from my_table cross join lateral (
  select
    json_array_elements_text(details->'dates') as dates,
    (json_array_elements_text(details->'quantities'))::numeric as quantity,
    (json_array_elements_text(details->'prices'))::numeric as price) as data
where details->>'city' = 'London'
group by quantity, price;

edited May 10 '20 at 10:52

answered May 10 '20 at 10:47

Abelisto

14,826
2
33
41

That's **exactly** what I want. Just a little problems: **1)** When using dates, it repeats some columns as it should be in group by clause. I think it is normal and for this reason I should remove that field, is that right? **2)** what if I use int instead of numeric? In that case should I also use another cast for the ratio line? – May 10 '20 at 11:21
@Jason 1) In my example `dates` column used in the inner subquery only, it should not to be the problem for the outer query where `group by` used. 2) `int/int` gives you `int` (`3/2 = 1`) so you need to cast at least one value in the formula: `3::float/2 = 1.5`; and using `float` type can to cause some [calculation errors](https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems) – Abelisto May 10 '20 at 11:35
Thanks for reply. The issue regarding to dates field is normal, because I have seen the same row repeat in relative query also. Regarding to numeric, what if I just replace numeric with float? Because, in numeric, I see extra 0's at the end of the values and lots of 0's instead of single 0. May it cause also calculation errors (I do not use int, just replace numeric with float)? Any help pls? – May 10 '20 at 11:44
@Jason It is the subject for another big discussion. In short: for the money related calculations always use `numeric` type, probably with particular precision like `numeric(10,2)` – Abelisto May 10 '20 at 11:58
What about giving your opinion in the following discussion :) [Should I use numeric or float to avoid calculaton prolems in PostgreSQL](https://stackoverflow.com/questions/61711547/should-i-use-numeric-or-float-to-avoid-calculaton-prolems-in-postgresql) – May 10 '20 at 12:11

Erwin Brandstetter · Answer 4 · 2020-05-11T01:39:48.817

Basically, what Abelisto said. Just a couple of improvements and some explanation:

SELECT to_date(d, 'DD.MM.YYYY') AS date -- ①
     , quantity, price
     , round(price / quantity, 4) AS ratio -- ③, ④
FROM   my_table
CROSS  JOIN LATERAL ( -- ②
   SELECT json_array_elements_text(details->'dates'     )          AS d -- ①
        , json_array_elements_text(details->'quantities')::int     AS quantity -- ③
        , json_array_elements_text(details->'prices'    )::numeric AS price -- ③
   ) AS data
WHERE  details->>'city' = 'London';

db<>fiddle here

① Date strings are interpreted depending on locale settings and session variables by default. Do it the clean way with to_date().

② Multiple set-returning functions in the SELECT list behave in surprising ways up until Postgres 10 if the number of resulting rows is not exactly the same for all. (Consider upgrading. In any case.) See:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

③ In your original query AVG(quantities/prices::float) makes no sense in combination with group by quantities, prices. Neither does quantities/prices on its own. I fixed as I saw fit, and threw in round() to format output.

④ If quantity can be 0 defend against division by 0 with NULLIF:

     , round(price / NULLIF(quantity, 0), 4) AS ratio

PostgreSql : Json Array to Rows using Lateral Join

4 Answers4

Linked