如何向JSON数组a3添加元素？已存在则更新值

Got it, let's tackle this problem step by step. You want to either append the {"arr3": "3"} object to the a3 array in your JSONB data, or update the value of arr3 if it already exists—no duplicates allowed. Here are two solid approaches depending on your needs:

Approach 1: Reconstruct the Array (Simplest, Order Doesn't Matter) #

This method rebuilds the a3 array by keeping all elements except any existing one with the arr3 key, then adds the new {"arr3": "3"} object. It guarantees only one arr3 entry exists, no matter the original state.

-- Define your original JSONB data
WITH original_data AS (
  SELECT '{ "a1": "e1", "a2": { "b1": "y1", "b2": "y2" }, "a3": [{ "arr1": "1" }, { "arr2": "2" }] }'::jsonb AS json_data
)
SELECT jsonb_set(
  json_data,
  '{a3}',
  (
    -- Keep all elements without the "arr3" key
    SELECT jsonb_agg(elem)
    FROM original_data, jsonb_array_elements(json_data->'a3') elem
    WHERE elem ? 'arr3' = FALSE
    -- Add the new/updated arr3 element
    UNION ALL
    SELECT '{"arr3": "3"}'::jsonb
  )
) AS updated_json
FROM original_data;

How it works: #

• jsonb_array_elements(json_data->'a3'): Splits the a3 array into individual rows of JSONB objects.
• WHERE elem ? 'arr3' = FALSE: Filters out any existing element that has the arr3 key.
• jsonb_agg(elem): Reaggregates the filtered elements back into an array.
• UNION ALL: Adds the new {"arr3": "3"} object to the filtered array.
• jsonb_set: Replaces the original a3 array with this new combined array.

Approach 2: Update Existing or Append (Preserves Element Order) #

If you need to keep the original position of an existing arr3 element (instead of moving it to the end), use this conditional approach. It checks if arr3 exists first, then updates it—otherwise, appends the new element.

-- Define your original JSONB data
WITH original_data AS (
  SELECT '{ "a1": "e1", "a2": { "b1": "y1", "b2": "y2" }, "a3": [{ "arr1": "1" }, { "arr2": "2" }, { "arr3": "old_value" }] }'::jsonb AS json_data
)
SELECT CASE
  -- Check if any element in a3 has the "arr3" key
  WHEN json_data @> '{"a3": [{"arr3": null}]}'::jsonb THEN
    jsonb_set(
      json_data,
      -- Get the index of the existing arr3 element and build the path
      array['a3', (
        SELECT idx::text
        FROM original_data, jsonb_array_elements(json_data->'a3') WITH ORDINALITY arr(elem, idx)
        WHERE elem ? 'arr3'
      ), 'arr3'],
      '"3"' -- Update the value to "3"
    )
  ELSE
    -- Append to the end of the a3 array (using "-" to target the last position)
    jsonb_set(json_data, '{a3, -}', '{"arr3": "3"}'::jsonb)
END AS updated_json
FROM original_data;

How it works: #

• json_data @> '{"a3": [{"arr3": null}]}'::jsonb: Uses the JSONB containment operator to check if a3 contains any element with the arr3 key (the null value acts as a wildcard here).
• jsonb_array_elements(...) WITH ORDINALITY: Gets both the array elements and their 1-based index positions.
• jsonb_set with the dynamic path: Updates the arr3 value in the existing element at its original index.
• '{a3, -}': Targets the last position of the a3 array to append the new element if no existing arr3 is found.

Test Both Scenarios #

• If arr3 doesn't exist: Both approaches will append {"arr3": "3"} to the end of a3.
• If arr3 already exists: Approach 1 moves arr3 to the end (since it's added after filtering), while Approach 2 keeps it in its original position and updates the value to 3.

内容的提问来源于stack exchange，提问作者Yavuz