A lateral join evaluates its inner query for each row produced by the preceding relation. Think of the inner query as a loop body controlled by the outer query. section 4.10
Use lateral joins when you need to:
- Unnest arrays or fan out data using values from the preceding query. section 4.10
- Select the most recent matching record for each outer row, such as a price valid on a power-production day. section 4.10
- Preserve unmatched outer rows with LEFT OUTER JOIN LATERAL. section 4.10
Pick selected hours from JSON arrays
WITH days AS (SELECT generate_series AS value FROM generate_series(7)), hours AS (SELECT unnest([8, 13, 18]) AS value), indexes AS (SELECT days.value * 24 + hours.value AS i FROM days, hours) SELECT date_trunc('day', now()) - INTERVAL '7 days' + INTERVAL (indexes.i || ' hours') AS ts, ghi.v AS 'GHI in W/m^2' FROM indexes, LATERAL (SELECT hourly.shortwave_radiation_instant[i+1] AS v FROM 'code/ch04/ghi_past_and_future.json') AS ghi ORDER BY ts; This produces 24 timestamp-and-irradiance rows. section 4.10
INSTALL json;
LOAD json;
WITH days AS (
SELECT generate_series AS value FROM generate_series(7)
), hours AS (
SELECT unnest([8, 13, 18]) AS value
), indexes AS (
SELECT days.value * 24 + hours.value AS i
FROM days, hours
)
SELECT date_trunc('day', now()) - INTERVAL '7 days' +
INTERVAL (indexes.i || ' hours') AS ts, #1
ghi.v AS 'GHI in W/m^2'
FROM indexes,
LATERAL (
SELECT hourly.shortwave_radiation_instant[i+1] #2
AS v
FROM 'code/ch04/ghi_past_and_future.json' #3
) AS ghi
ORDER BY ts;
Inspect the complete DuckDB query that combines generated days and hours with a lateral JSON lookup. section 4.10
