Partial update of json field in postgres

Question

In Postgres, I have a table like this:

CREATE TABLE storehouse ( user_id bigint NOT NULL, capacity integer NOT NULL, storehouse json NOT NULL, last_modified timestamp without time zone NOT NULL, CONSTRAINT storehouse_pkey PRIMARY KEY (user_id) ) 

And storehouse.storehouse stores this data:

 { "slots":[ { "slot" : 1, "id" : 938 }, { "slot" : 2, "id" : 127 }, ] } 

The fact is that I want to update storehouse.storehouse.slots[2] , but I have no idea how to do this.

I know how to change the entire storehouse.storehouse field, but I'm interested, since Postgres supports the json type, it must support partial change, otherwise there will be no difference between the json type and the text type. (I know that json type also has type checking that is different from text )

Answer 1

JSON indexing and partial updates are not currently supported. JSON support in PostgreSQL 9.2 is rudimentary, limited to checking JSON and converting strings and arrays to JSON. Inside json really pretty much just text .

Work continues on improvements such as partial updates, indexing, etc. Regardless of the fact that PostgreSQL will not be able to avoid overwriting the entire row when changing the JSON value, since this is inherent in the MVCC concurrency model. The only way to do this would be to split the JSON values ​​into several tuples laterally, for example, TOAST tables - something that is possible, but probably poor execution and this is very far from being considered at this stage.

As Chris Traverse points out, you can use PL / V8 functions or functions in other json-enabled languages ​​like Perl or Python to extract values, and then create expression indices for those functions.

Answer 2

Starting with PostgreSQL 9.5, there is a function called jsonb_set that takes as input parameters:

• json object
• array indicating the path (keys and plug)
• new value to be saved (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)