Skip to content

Instantly share code, notes, and snippets.

@tomekbielaszewski
Created July 30, 2019 11:43
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tomekbielaszewski/dfd388635fa58db189955507e43de22f to your computer and use it in GitHub Desktop.
Save tomekbielaszewski/dfd388635fa58db189955507e43de22f to your computer and use it in GitHub Desktop.
PostgreSQL: Removing nested JSON property
-- before:
-- my_json = {some_var: 10, some_nested_ob: {some_var: 10, another_var: 20}}
-- after:
-- my_json = {some_var: 10, some_nested_ob: {another_var: 20}}
WITH subquery AS (
SELECT my_json #- '{some_nested_ob,some_var}' AS updated_json,
id
FROM my_table
WHERE (my_json -> 'some_nested_ob') -> 'some_var' IS NOT NULL
)
UPDATE my_table
SET my_json=subquery.updated_json
FROM subquery
WHERE my_table.id = subquery.id
RETURNING my_table.id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment