Understanding of LATERAL Joins and UNNEST Arrays for JSON Aggregation in PostgreSQL
In this blog, we will demonstrate PostgreSQL’s tools to show how to use LATERAL Joins and UNNEST Arrays for JSON Aggregation, all together with a real-world example:
LATERAL Joins in PostgreSQL(geeksforgeeks)
A LATERAL join enables a subquery to depend on columns from earlier tables within the same query. This SQL enhancement offers greater adaptability for sophisticated and context-aware data handling. This flexibility streamlines complicated or detailed queries and boosts their efficiency and performance by minimizing redundant code and enhancing readability.
Syntax:
SELECT columns
FROM table1
JOIN LATERAL subquery AS alias ON condition;
Understanding JSON Aggregation(neon.tech)
The aggregate PostgreSQL function jsonb_agg() collects values from multiple rows and outputs them as a single JSON array. This functionality has been proven beneficial in scenarios involving JSON aggregation followed by array unnesting.
Syntax:
jsonb_agg(expression ORDER BY sort_expression [ASC | DESC] [NULLS { FIRST | LAST }]) -> json
Understanding the unnest function (hevodata.com)
PostgreSQL's unnest function, categorised under array functions, transforms an array into a set of rows, effectively creating a table-like structure from the array elements. This capability is useful for expanding array data into individual values or converting arrays into a row-based format. PostgreSQL provides the system function unnest() to perform this operation, allowing the expansion of an array into a specified number of rows.
Syntax:
SELECT unnest(ARRAY[10, 11, 12, 13, 14, 15, 16]);
Example :
Analyze Elevated ALT Liver Enzymes in Pregnancy Using SQL with LATERAL and JSON Aggregation.
Identifies women with elevated ALT levels.
Assign risk levels based on trimester and other conditions.
Aggregate related risk factors.
Visualize these as a materialized view for faster analysis.
Let’s break down the query step by step :
A recent study (PubMed, Nature) found that approximately 27% of pregnant women exhibit elevated levels of ALT (alanine transaminase), which may suggest liver stress. Elevated ALT is frequently linked to complications such as pre-eclampsia, twin pregnancies, stillbirths, and miscarriages.
To begin, we will establish a MATERIALIZED VIEW.
A view that stores data that comes from the base tables, which gives snapshots of the data table and query performance, is also faster.
1. Identifies women with elevated ALT levels
Identify pregnant women who have elevated ALT liver enzyme levels, as 27% had increased ALT levels.
2. Assigns a risk level :
To check their risk level, trimester-wise, we divide the trimesters into gestation age.
1. High risk
2 .Low risk
3. Aggregate pregnancy-related risk factors using an array UNNEST() and LATERAL.
Combine all the data using
1 . jsonb_agg; In case you have to export data
Or array_agg; Limited to PostgreSQL use
2. Then expand them using UNNEST() + LATERAL join.
4.Visualize these as a materialized view for faster analysis.
Use a select statement to visualize materialized view and before that refresh the materialized view to get the latest data.
CREATE MATERIALIZED VIEW alt_classification AS
SELECT
mhi.participant_id,
bm.alt_change_percent,
Identify pregnant women who have elevated ALT liver enzyme levels, as 27% had increased ALT levels
CASE
WHEN bm.alt_change_percent > 27
THEN 'Elevated'
ELSE 'Normal'
END AS alt_level_Status,
Check their risk level, trimester-wise divide the trimesters into gestation age
CASE
WHEN pi.gestational_age_v1 ~ '^\d+\+\d+$'
THEN
CASE
WHEN (split_part(pi.gestational_age_v1, '+', 1)::numeric +
split_part(pi.gestational_age_v1, '+', 2):: numeric / 7) BETWEEN 1 AND 13
THEN 'First'
WHEN (split_part(pi.gestational_age_v1, '+', 1)::numeric +
split_part(pi.gestational_age_v1, '+', 2):: numeric / 7) BETWEEN 14 AND 27
THEN 'Second'
WHEN (split_part(pi.gestational_age_v1, '+', 1)::numeric +
split_part(pi.gestational_age_v1, '+', 2)::numeric / 7) >= 28
THEN 'Third'
ELSE 'Unknown'
END
ELSE 'Unknown'
END AS trimester,
Display biomarkers
mhi."Pre-eclampsia" = 1 AS has_preeclampsia,
pi.twins =1 AS has_twins,
pi."Still-birth" = 1 AS has_stillbirth,
pi."Miscarried 10" = 1 AS has_miscarried_10,
pi.miscarriage_after_28_weeks = 1 AS has_miscarriage_after_28,
pi.miscarriage_before_28_weeks = 1 AS has_miscarriage_before_28,
Risk level using all fields above
CASE
WHEN bm.alt_change_percent > 27 – if elevated
AND ( mhi."Pre-eclampsia" = 1
OR pi.twins = 1
OR pi."Still-birth" = 1
OR pi."Miscarried 10" = 1
OR pi.miscarriage_after_28_weeks = 1
OR pi.miscarriage_before_28_weeks = 1 )
THEN 'High Risk'
ELSE 'Low_risk'
END AS alt_risk_level,
-- array_agg(risk_factor)FILTER(WHERE risk_factor IS NOT NULL )AS alt_risk_factors or
jsonb_agg(risk_factor) FILTER (WHERE risk_factor IS NOT NULL) AS risk_factors
FROM biomarkers bm
JOIN maternal_health_info mhi on mhi.participant_id = bm.participant_id
JOIN pregnancy_info pi on pi.participant_id = mhi.participant_id
LEFT JOIN LATERAL( -- LATERAL gives the subquery access to the current row.
SELECT UNNEST(ARRAY[ -- SELECT unnest(ARRAY[10, 11, 12, 13, 14, 15, 16]);
CASE -- pregnancy-related to check risk factors
WHEN mhi."Pre-eclampsia" = 1
THEN 'Pre-eclampsia'
ELSE NULL
END,
CASE
WHEN pi.twins = 1
THEN 'Twins'
ELSE NULL
END,
CASE
WHEN pi."Still-birth" = 1
THEN 'Still-birth'
ELSE NULL
END,
CASE
WHEN pi."Miscarried 10" = 1
THEN 'Miscarried 10'
ELSE NULL
END,
CASE
WHEN pi.miscarriage_after_28_weeks = 1
THEN 'miscarriage_after_28_weeks'
ELSE NULL
END,
CASE
WHEN pi.miscarriage_before_28_weeks = 1
THEN 'miscarriage_before_28_weeks'
ELSE NULL
END
]) AS risk_factor
) AS risk_factors ON TRUE
GROUP BY
mhi.participant_id,
bm.alt_change_percent,
pi.gestational_age_v1,
mhi."Pre-eclampsia",
pi.twins,
pi."Still-birth",
pi."Miscarried 10",
pi.miscarriage_after_28_weeks ,
pi.miscarriage_before_28_weeks;
visualization of materialized view
REFRESH MATERIALIZED VIEW alt_classification;
Query to display the columns from the views
SELECT * FROM alt_classification;
Conclusion:
PostgreSQL’s lateral joins, UNNEST, and JSON aggregation allow us to perform clean, powerful data transformations, which are ideal for analytical queries as well as machine learning preprocessing.
Notes and Considerations:
ALT Threshold Interpretation: The study (PubMed, Nature) defines a 27% increase in ALT level, alongside considering it with other biomarkers, we can calculate risk levels.
Gestational Age Format: Ensure it's consistently stored in 'weeks+days' weeks format.
Indexing: Consider indexing participant_id or alt_change_percent for performance on a large dataset.
JSONB and Array Support: You can swap array_agg() with jsonb_agg() to return true JSON arrays.
A Business Logic Engine, or Business Rules Engine (BRE) Integration: This view supports automation, rules engines, and real-time alerting without the need for additional scripting.
For example,
Trigger: We can use this materlized view for an advanced performance like sending alerts
to doctors or patients.
Dataset Note: Refer to any gestational diabetes dataset of pregnant women for the study which includes all relevant biomarkers.
IMPORTANT: This example serves only to illustrate our topic.
References :
