Comprehensive Guide to Modifying Fields in PostgreSQL JSON Data Type

Technical Evolution of JSON Data Type Operations

PostgreSQL introduced JSON data type support starting from version 9.2, but early versions were primarily limited to query functionality. With the release of version 9.3, while basic field extraction capabilities were provided, modification operations remained significantly constrained. Developers attempting direct JSON field modifications using UPDATE statements encountered syntax errors, highlighting the limitations of JSON operation capabilities at that time.

JSONB Operation System in PostgreSQL 9.5+

PostgreSQL version 9.5 marked a significant leap in JSON operation capabilities, introducing a comprehensive set of operation functions specifically designed for the JSONB data type. Compared to the standard JSON type, JSONB employs a binary storage format, offering distinct advantages in both query performance and functional support.

Basic Field Setting Operations

The merge operator || enables simple key-value setting:

SELECT jsonb '{"a":1}' || jsonb '{"b":2}';

The execution of this code produces the result jsonb '{"a":1,"b":2}'. For dynamic key-value setting, combine with the jsonb_build_object function:

SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>');

Deep Hierarchical Field Modification

The jsonb_set function is specifically designed for field modification within nested JSON structures:

SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}');

This function accepts four parameters: target JSONB object, path array, new value, and optional creation flag. The path array uses text array notation and supports both forward and backward indexing:

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true)

When the path points to a non-existent positive array index, the system automatically appends the new element to the end of the array:

SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true);

Array Insertion Operations

PostgreSQL 9.6 introduced the jsonb_insert function, specifically designed for element insertion within arrays:

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2');

The core parameters of this function include:

jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean default false)

By adjusting the insert_after parameter, insertion position can be controlled:

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true);

Key-Value Deletion Operations

The minus operator enables deletion of specified keys or array indices:

SELECT jsonb '{"a":1,"b":2}' - 'a';

For deletion operations within nested structures, the #- operator can be used:

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}';

Custom Function Extension Solutions

In versions prior to PostgreSQL 9.5, JSON field modification functionality could be achieved through custom function creation. The following demonstrates a basic field setting function implementation:

CREATE OR REPLACE FUNCTION json_object_set_key(
  json_val json,
  key_to_set TEXT,
  value_to_set anyelement
)
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT concat('{', string_agg(to_json(key) || ':' || value, ','), '}')::json
FROM (SELECT *
      FROM json_each(json_val)
      WHERE key <> key_to_set
      UNION ALL
      SELECT key_to_set, to_json(value_to_set)) AS fields
$function$;

Multi-field Batch Updates

Extending the basic function to support simultaneous updates of multiple fields:

CREATE OR REPLACE FUNCTION json_object_set_keys(
  json_val json,
  keys_to_set TEXT[],
  values_to_set anyarray
)
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT concat('{', string_agg(to_json(key) || ':' || value, ','), '}')::json
FROM (SELECT *
      FROM json_each(json_val)
      WHERE key <> ALL (keys_to_set)
      UNION ALL
      SELECT DISTINCT ON (keys_to_set[index])
             keys_to_set[index],
             CASE
               WHEN values_to_set[index] IS NULL THEN 'null'::json
               ELSE to_json(values_to_set[index])
             END
      FROM generate_subscripts(keys_to_set, 1) AS keys(index)
      JOIN generate_subscripts(values_to_set, 1) AS values(index)
      USING (index)) AS fields
$function$;

Update Existing Fields Only

For scenarios requiring updates only to existing fields, a specialized update function is provided:

CREATE OR REPLACE FUNCTION json_object_update_key(
  json_val json,
  key_to_set TEXT,
  value_to_set anyelement
)
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT CASE
  WHEN (json_val -> key_to_set) IS NULL THEN json_val
  ELSE (SELECT concat('{', string_agg(to_json(key) || ':' || value, ','), '}')
        FROM (SELECT *
              FROM json_each(json_val)
              WHERE key <> key_to_set
              UNION ALL
              SELECT key_to_set, to_json(value_to_set)) AS fields)::json
END
$function$;

Practical Application Scenario Analysis

In real database operations, JSON field modifications typically involve complex business logic. The following complete update example demonstrates how to integrate these operations within applications:

UPDATE test_table 
SET json_column = jsonb_set(json_column, '{user,profile,email}', '"new@example.com"')
WHERE id = 123;

This operational pattern is particularly suitable for scenarios requiring frequent partial field updates, such as configuration data and user preference settings. Through appropriate use of JSONB operation functions, read-write overhead for entire objects can be significantly reduced, thereby improving system performance.

Performance Optimization Recommendations

When utilizing JSON operation functions, the following performance optimization considerations are essential:

• Prefer JSONB type over JSON type for superior query performance
• For frequently updated fields, consider extracting them as separate table columns
• Employ appropriate indexing strategies, such as GIN indexes, to accelerate JSON field queries
• Utilize transactions for batch operations to ensure data consistency

Version Compatibility Considerations

Significant differences exist in JSON support across different PostgreSQL versions. In versions 9.3-9.4, field modification primarily relied on custom functions; starting from 9.5, built-in functions provided comprehensive operational support; versions 9.6 and subsequent releases further enhanced array operation capabilities. Developers must carefully consider target environment version constraints when selecting technical solutions.