CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-9.5
user5871859
user5871859

Reputation:

PostgreSql : Json Array to Rows using Lateral Join

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.

Upvotes: 1

Views: 9246

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

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:

③ 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

Or use json(b)_populate_record() in Posatgers 13+:

WITH cte AS MATERIALIZED (
   SELECT jsonb_populate_record(null::pg_temp.my_type, t.details) r
   FROM   my_table t
   WHERE  t.details @> '{"city": "London"}'  -- can use GIIN index
   )
SELECT to_date(t.date, 'DD.MM.YYYY') AS date
     , quantity, price
     , round(price / quantity, 4) AS ratio
FROM   cte, unnest((r).dates, (r).quantities, (r).prices) AS t(date, quantity, price);

fiddle

Upvotes: 3

Abelisto
Abelisto

Reputation: 15624

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;

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

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

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42853

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

Upvotes: 3

Related Questions