PostgreSQL JSONB中如何为嵌套在两层数组内的对象添加新属性

Got it, let's break down how to add a new property to specific SubEntries objects nested inside two arrays in your PostgreSQL JSONB column. The key here is targeting entries based on ExtId.Id while preserving the rest of the JSON structure.

Method 1: Compatible with PostgreSQL 9.6+ (Using CTEs & Array Aggregation) #

This approach works for older PostgreSQL versions by expanding the nested arrays, modifying the target objects, then rebuilding the JSON structure.

Suppose you want to add a property NewProperty: "NewValue" to all SubEntries where ExtId.Id = '10'. Here's the full SQL:

WITH updated_subentries AS (
    SELECT
        seq_id,
        -- Unpack the outer Entries array
        jsonb_array_elements(data->'Calendar'->'Entries') AS entry,
        -- Unpack the inner SubEntries array, and modify matching items
        jsonb_array_elements(entry->'SubEntries') AS subentry,
        CASE
            WHEN subentry->'ExtId'->>'Id' = '10' THEN
                -- Add new property (use || to merge JSONB objects)
                subentry || '{"NewProperty": "NewValue"}'::jsonb
            ELSE
                subentry
        END AS updated_subentry
    FROM events
    -- Filter rows that actually contain the target ExtId to avoid full table scans
    WHERE data @> '{"Calendar": {"Entries": [{"SubEntries": [{"ExtId": {"Id": "10"}}]}]}}'::jsonb
),
updated_entries AS (
    SELECT
        seq_id,
        -- Rebuild the SubEntries array with modified items
        jsonb_build_object(
            'Id', entry->>'Id',
            'SubEntries', jsonb_agg(updated_subentry),
            'OrderResult', entry->'OrderResult'
        ) AS updated_entry
    FROM updated_subentries
    GROUP BY seq_id, entry->>'Id', entry->'OrderResult'
),
updated_calendar AS (
    SELECT
        seq_id,
        -- Rebuild the full top-level JSON object
        jsonb_build_object(
            'Id', data->>'Id',
            'Calendar', jsonb_build_object(
                'Entries', jsonb_agg(updated_entry),
                'OtherThingsArray', data->'Calendar'->'OtherThingsArray'
            )
        ) AS updated_data
    FROM updated_entries
    JOIN events USING(seq_id)
    GROUP BY seq_id, data->>'Id', data->'Calendar'->'OtherThingsArray'
)
UPDATE events
SET data = updated_calendar.updated_data
FROM updated_calendar
WHERE events.seq_id = updated_calendar.seq_id;

Key Notes for This Method: #

• The || operator safely merges JSONB objects—if the property already exists, it will be overwritten (adjust if you need to avoid that).
• The initial WHERE clause uses the @> containment operator to quickly find rows with the target ExtId, which is much more efficient than scanning every row.
• If you need to use dynamic values (e.g., from another column), replace the static JSON string with jsonb_build_object('NewProperty', your_column_name).

Method 2: Simplified for PostgreSQL 14+ (Using JSONPath) #

PostgreSQL 14 introduced jsonb_path_set, which lets you use JSONPath expressions to directly target nested objects. This is far more concise:

UPDATE events
SET data = jsonb_path_set(
    data,
    -- JSONPath to target SubEntries where ExtId.Id equals '10'
    '$.Calendar.Entries[*].SubEntries[*] ? (@.ExtId.Id == "10")',
    -- The new property to add (merge with existing object)
    '{"NewProperty": "NewValue"}'::jsonb,
    true -- Allow adding new properties (set to false to only modify existing ones)
)
WHERE data @> '{"Calendar": {"Entries": [{"SubEntries": [{"ExtId": {"Id": "10"}}]}]}}'::jsonb;

Why This Works: #

• The JSONPath $.Calendar.Entries[*].SubEntries[*] matches all SubEntries in any Entries item.
• The filter ? (@.ExtId.Id == "10") narrows it down to only the objects you want to modify.
• jsonb_path_set merges the new property into the matched objects, preserving all other existing data.

Testing the Changes #

After running either query, you can verify the update with:

SELECT data->'Calendar'->'Entries'->0->'SubEntries'->0 FROM events WHERE seq_id = [your_seq_id];

You should see the new NewProperty in the target SubEntries object.

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