Skip to content

Instantly share code, notes, and snippets.

@ker0x

ker0x/update_from_aggregate_sum_json.sql

Created September 7, 2022 10:24
Show Gist options
  • Save ker0x/55e78fbb1807070b06ce5b99bdb25194 to your computer and use it in GitHub Desktop.
Save ker0x/55e78fbb1807070b06ce5b99bdb25194 to your computer and use it in GitHub Desktop.
Download ZIP
Set a column value with an aggregate sum from a JSON column
Raw
update_from_aggregate_sum_json.sql
WITH estimate_inter_durations AS (
SELECT (json_array_elements(estimate_inter.sequences)->>'duration')::float AS duration, estimate_inter.id
FROM estimate_inter
INNER JOIN estimate ON estimate.id = estimate_inter.id
WHERE estimate.id = estimate_inter.id
)
UPDATE estimate
SET duration_value = (
SELECT SUM(duration)
FROM estimate_inter_durations
WHERE estimate.id = estimate_inter_durations.id
GROUP BY estimate_inter_durations.id
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment