Updating JSON in postgres

Question

I have a table with a json field (NOT json-b) and need to remove a key that exists with some records (or set the value to '').

How can I update this table to do so using SQL?

I've seen this question, but it feels like there should be an easier way for this simple thing.

All keys on the root level, no nesting, right? And always provide your version of Postgres, please. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 5, 2015 at 13:11

Erwin Brandstetter · Accepted Answer · 2023-10-29 22:03:25Z

2

First: if you are updating values a lot, json may be a bad choice in your database design:

How to perform update operations on columns of type JSONB

Pure SQL:

UPDATE tbl
SET    json_col = (
   SELECT concat('{', string_agg(to_json(j.key) || ':' || j.value, ','), '}')::json
   FROM   json_each(json_col) j
   WHERE  j.key <> 'delete_this_key'
   )
WHERE  json_col->>'delete_this_key' <> ''; -- only applicable rows!

How do I modify fields inside the new PostgreSQL JSON datatype?

edited Oct 29, 2023 at 22:03

answered Mar 5, 2015 at 13:30

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating JSON in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related