Oracle JSON_MERGEPATCH updates

Reference JSON object.

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            },
        ]
    }
}

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates[1]":{"study_date":"30-SEP-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

after update study_dates[1] a new node gets added

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "30-SEP-2099"
        }
    }
}

-- Where condition on this does not work

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates[1]":{"study_date":"31-OCT-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004
AND JSON_VALUE(STUDENT_DATA, '$.study_schedule.study_dates[1].study_date') = '30-SEP-2099';
0 rows updated.

--This works

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates[1]":{"study_date":"21-OCT-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

--Add a new node study_dates[2] under study_schedule. gets added

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates[2]":{"study_date":"31-OCT-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

--Add a new main node study_schedule

 
UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule[1]":{"study_dates":{"study_date":"01-JAN-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

output will be as below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "study_schedule[1]": {
        "study_dates": {
            "study_date": "01-JAN-2099"
        }
    }
}

---------------------------------------------------- Adding a new date schedule to the JSON

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"exam_schedule":{"exam_dates":{"exam_date":"01-JAN-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-JAN-2099"
        }
    }
}

--Update for single schedule works with or without a where clause --update exam date schedule to 01-FEB-2099

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"exam_schedule":{"exam_dates":{"exam_date":"01-FEB-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

output will be as below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

--this update does not work

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"exam_schedule":{"exam_dates":{"exam_date":"01-MAR-2099"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004
AND JSON_VALUE(STUDENT_DATA, '$.exam_schedule.exam_dates.exam_date') = '01-FEB-2099';

--This update will delete all other study_date schedules under study_schedule.study_dates

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":{"study_date[0]":"30-SEP-2051"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

output is as below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": {
            "study_date[0]": "30-SEP-2051"
        },
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

--where condition does not work

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":{"study_date[0]":"31-OCT-2051"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004
AND JSON_VALUE(STUDENT_DATA, '$.study_schedule.study_dates.study_date[0]') = '30-SEP-2051';

--This will add a new node study_date[1] under study_schedule.study_dates

UPDATE STUDENT_PROFILE
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":{"study_date[1]":"31-OCT-2051"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

output is as below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": {
            "study_date[0]": "30-SEP-2051",
            "study_date[1]": "31-OCT-2051"
        },
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

--This will update study_date[1] from 31-OCT-2051 to 31-OCT-2052

UPDATE STUDENT_PROFILE 
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":{"study_date[1]":"31-OCT-2052"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": {
            "study_date[0]": "30-SEP-2051",
            "study_date[1]": "31-OCT-2052"
        },
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

Thus we can see that in JSON_MERGEPATCH update, where condition does not work on multiple records in a schedule. 

If the lowest branch in the schedule had multiple nodes, and the nodes are numbered, JSON_MERGEPATCH update will work by specifying the node number to be updated in the JSON_MERGEPATCH without giving any where condition at the schedule node level. 

If the particular node does not exist, JSON_MERGEPATCH will add that node to the JSON srructure as per the JSON_MERGEPATCH hierarchy/clause specified.

If the nodes are not numbered, and all the nodes in the json schedule have the same tag_name, updating the entire schedule also will only modify the json to have a single tag.

Reference JSON with array elements defined

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates[FEB]": {
            "study_date[1]": "11-FEB-2026",
            "study_date[2]": "12-FEB-2026"
        },
        "study_dates[MAR]": {
            "study_date[1]": "11-MAR-2026"
        },
        "study_dates[APR]": {
            "study_date[1]": "11-APR-2026"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date[1]": "01-MAY-2099"
        }
    },
    "study_schedule[1]": {
	    "subject": "geography",
        "study_dates[FEB]": {
            "study_date[1]": "01-FEB-2026"
        },
		"study_dates[MAR]": {
            "study_date[1]": "01-MAR-2026",
			"study_date[2]": "02-MAR-2026"
        }
    }
}

In this example, student with roll_no 10004, Mr Rafael Nadal has prepared a study schedule for 2 subjects, history and geography for the months of Feb and Mar. But due to his hectic tennis schedule, his study schedule keeps changing, and so he has to constantly update the study dates. He also has to update the subject to combine more than 1 subject on some dates. But now with each array elerment defined he can easily do that.

Following are the updates he wants to do

Update subject "History" to "History and Civics".

Update study dates in Feb from 

11-FEB-2026 to 21-FEB-2026 and from 12-FEB-2026 to 22-FEB-2026

Update subject "Geography" to "Geography and Environmental Studies".

Update study dates in Feb for Geography from 

01-FEB-2026 to 06-FEB-2026 and add an extra study date for geography i.e. 07-FEB-2026

We can achieve this with 2 update statements, 1 update for each schedule in the JSON.

Below is the update for the history schedule.

UPDATE STUDENT_PROFILE 
SET STUDENT_DATA = JSON_MERGEPATCH(
    STUDENT_DATA, 
    '{"study_schedule": {"subject":"history and civics", "study_dates[FEB]": {"study_date[1]": "21-FEB-2026", "study_date[2]": "22-FEB-2026"}}}'
)
WHERE STUDENT_ROLL_NO = 10004;

Below is the update for the geography schedule.

UPDATE STUDENT_PROFILE 
SET STUDENT_DATA = JSON_MERGEPATCH(
    STUDENT_DATA, 
     '{"study_schedule[1]": {"subject":"geography and environmental studies", "study_dates[FEB]": {"study_date[1]": "06-FEB-2026", "study_date[2]": "07-FEB-2026"}}}'
)
WHERE STUDENT_ROLL_NO = 10004;

If the nodes are not numbered, and all the nodes in the json schedule have the same tag_name, updating the entire schedule also will only modify the json to have a single tag.

To explain this is more detail with an illustration below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date": "01-Jul-2024"
            },
            {
                "study_date": "05-Jul-2024"
            },
			{
                "study_date": "06-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

I now try to update the third study_date in the schedule from 06-Jul-2024 to 10-Jul-2024

UPDATE STUDENT_PROFILE 
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":
			{"study_date":"01-Jul-2024",
			"study_date": "05-Jul-2024",
			"study_date": "10-Jul-2024"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

after JSON_MERGEPATCH update, the modified JSON will have only the lastest tag for study_date

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": {
            "study_date": "10-Jul-2024"
        },
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

As demonstrated earlier, we can either number the nodes in the schedule or given each tag in the schedule an unique identifier. Illustration is below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": [
            {
                "study_date_1": "01-Jul-2024"
            },
            {
                "study_date_2": "05-Jul-2024"
            },
			{
                "study_date_3": "06-Jul-2024"
            }
        ],
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

I identify each tag in the schedule as study_date_1, study_date_2 and study_date_3. I now update the third study_date in the schedule from 06-Jul-2024 to 10-Jul-2024

UPDATE STUDENT_PROFILE 
SET STUDENT_DATA = JSON_MERGEPATCH(STUDENT_DATA, '{"study_schedule":{"study_dates":
			{"study_date_1":"01-Jul-2024",
			"study_date_2": "05-Jul-2024",
			"study_date_3": "10-Jul-2024"}}}' RETURNING CLOB)
WHERE STUDENT_ROLL_NO = 10004;

after JSON_MERGEPATCH update, the modified JSON will have now have all 3 tags and show the updated value for study_date_3 as below

{
    "student_roll_no": 10004,
    "standard": 9,
    "first_name": "Rafael",
    "last_name": "Nadal",
    "location": "Spain",
    "study_schedule": {
        "subject": "history",
        "study_dates": {
            "study_date_1": "01-Jul-2024",
            "study_date_2": "05-Jul-2024",
            "study_date_3": "10-Jul-2024"
        },
        "study_dates[1]": {
            "study_date": "21-OCT-2099"
        },
        "study_dates[2]": {
            "study_date": "31-OCT-2099"
        }
    },
    "exam_schedule": {
        "exam_dates": {
            "exam_date": "01-FEB-2099"
        }
    }
}

But what if progranatically, based on some logic a particular schedule element is changed? and we want to update only that element in the JSON.

A PLSQL approach to update that schedule element can be considered.

A sample PLSQL block to parse elements in a json array

DECLARE
    p_in VARCHAR2(4000) := '[{"student_roll_no":10004},{"student_roll_no":10005}]';
    l_ja json_array_t;
    l_jo json_object_t;
 
BEGIN
    
    l_ja := json_array_t.parse(p_in);
    for i in 0..l_ja.get_size - 1 loop
      l_jo := json_object_t(l_ja.get(i));
      dbms_output.put_line('l_jo ' || l_jo.to_string);
    end loop;

END;

Output for the same is as below

l_jo {"student_roll_no":10004}
l_jo {"student_roll_no":10005}

To be continued...

Thought for the day

Do not seek from the Lord high office,

  or the seat of honor from the king.

Do not assert your righteousness before the Lord,

  or display your wisdom before the king.

Do not seek to become a judge,

  or you may be unable to root out injustice.

You may be partial to the powerful,

  and so mar your integrity.

Sirach 7:4-6