You can use the following statements to modify a JSON column:
- UPDATE. You can use the UPDATE statement to modify values of a JSON column in the SET clause.You can only update the JSON column, not individual portions of the JSON instance. Also, when referencing a JSON column in its entirety, the input format is the same as the INSERT statement.
- MERGE. You can use the MERGE statement to:
- Modify a JSON column based on the WHEN MATCHED THEN UPDATE clause.
- Insert values into a JSON column based on the WHEN NOT MATCHED THEN INSERT clause.The following MERGE statement clauses are supported on JSON functions and methods:
- The ON clause can be used to condition on a JSON instance.
- The USING clause can be used to select the JSON column value.
Setting Up the Update Examples
Create and populate table(s) to use in subsequent example(s).
CREATE TABLE my_table (eno INTEGER, edata JSON(100));
INSERT INTO my_table (1, NEW JSON('{"name" : "Cameron", "age" : 24}'));
Example: UPDATE and SET
The following statement sets the edata column value and updates the table.
UPDATE my_table
SET edata = NEW JSON('{"name" : "Justin"}')
WHERE edata.JSONExtractValue('$.name') = 'Cameron';
Result: To see the result, run: SELECT edata FROM my_table;
edata
-----
{"name" : "Justin"}
Example: UPDATE Column Value
The following statement sets the edata column value and updates the table.
UPDATE my_table
SET edata = '{"name" : "George"}';
Result: To see the name has been updated, run: SELECT edata FROM my_table;
edata
-----
{"name" : "George"}
Setting Up the Merge Examples
Create and populate table(s) to use in subsequent example(s).
CREATE TABLE my_table (eno INTEGER, edata JSON(100));
INSERT INTO my_table(1,'{"name" : "Justin", "phoneNumber" : 8584852611}');
INSERT INTO my_table(2,'{"name" : "Cameron", "phoneNumber" : 8584852612}');
CREATE TABLE my_table2 (eno INTEGER, edata JSON(100));
INSERT INTO my_table2(1,'{"age":24}');
INSERT INTO my_table2(3,'{"age":34}');
Example: MERGE When Matched Then Update
The example shows how to use the MERGE WHEN MATCHED THEN UPDATE clause.
MERGE INTO my_table
USING (SELECT eno, edata FROM my_table2) AS S(a,b)
ON eno = S.a AND
CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Justin'
WHEN MATCHED THEN UPDATE
SET edata = '{"name" : "Kevin", "phoneNumber" : 8584852613}';
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
---------
1 {"name" : "Kevin", "phoneNumber" : 8584852613}
2 {"name" : "Cameron", "phoneNumber" : 8584852612}
Example: MERGE When Not Matched Then Insert
The example shows how to use the MERGE WHEN NOT MATCHED THEN INSERT clause.
MERGE INTO my_table
USING (SELECT eno, edata FROM my_table2) AS S(a,b)
ON eno = S.a AND
CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Mike'
WHEN NOT MATCHED THEN INSERT (eno, edata)
VALUES (S.a, NEW JSON('{"name" : "Mike", "phoneNumber" : 8584852614}'));
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
-----------
1 {"name" : "Kevin", "phoneNumber" : 8584852613}
2 {"name" : "Cameron", "phoneNumber" : 8584852612}
2 {"name" : "Mike", "phoneNumber" : 8584852614}