partial update json field in postgres

JSON indexing and partial updates are not currently supported. The JSON support in PostgreSQL 9.2 is rudimentary, limited to validating JSON and to converting rows and arrays to JSON. Internally, json is indeed pretty much just text.

There's ongoing work for enhancements like partial updates,indexing, etc. No matter what, though, PostgreSQL won't be able to avoid rewriting the whole row when part of a JSON value changes, because that's inherent to the MVCC model of concurrency. The only way to make that possible would be to split JSON values out into multiple tuples in a side relation, like TOAST tables - something that's possible, but likely to perform poorly and that's very far from being considered at this point.

As Chris Travers points out, you can use PL/V8 functions or functions in other languages with json support like Perl or Python to extract values, then create expression indexes on those functions.

Since PostgreSQL 9.5, there a function called jsonb_set which takes as input parameters:

• a JSON object
• an array indicating the path (keys and subkeys)
• the new value to be stored (also a JSON object)

Example:

 # SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb,            '{contact,phone}',            '"07900 112233"'::jsonb);                                  jsonb_replace                                   --------------------------------------------------------------------------------  {"name": "James", "contact": {"fax": "01987 543210", "phone": "07900 112233"}} (1 row)