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.

Answer 1

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!

Related:

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