Oracle 12.2中通过CHECK约束限制JSON列仅含指定对象

Absolutely, you can enforce this requirement with a CHECK constraint in Oracle 12.2—let’s break down the best approaches based on your existing table setup.

Approach 1: Leverage Your Existing Constraints (Most Concise) #

Since you already have checkJson_F1 and checkJson_F2 ensuring FIELD1 and FIELD2 are always present, the only extra check you need is that the JSON object has exactly 2 top-level keys. If there are only 2 keys, and both required fields exist, there’s no room for unwanted keys like FIELD3.

Use the json_keys function to get all top-level keys as a JSON array, then validate its length with json_array_length:

ALTER TABLE myData 
ADD CONSTRAINT checkJson_OnlyAllowedKeys 
CHECK (json_array_length(json_keys(json_data)) = 2);

Test It Out

If you try inserting a row with an extra field:

INSERT INTO myData(id, json_data) VALUES(1, '{"FIELD1" : "abc", "FIELD2" : "def", "FIELD3" : "ghi"}');

You’ll get a constraint violation error—exactly the behavior you want.

Approach 2: Standalone Constraint (No Dependency on Existing Checks) #

If you want a self-contained constraint that doesn’t rely on the existing FIELD1/FIELD2 checks, you can verify that the set of keys is exactly FIELD1 and FIELD2 (regardless of their order in the JSON string).

Use json_equal to compare the array of keys from json_keys to both possible ordered arrays of allowed keys:

ALTER TABLE myData 
ADD CONSTRAINT checkJson_OnlyAllowedKeys 
CHECK (
    json_equal(json_keys(json_data), '["FIELD1","FIELD2"]') 
    OR json_equal(json_keys(json_data), '["FIELD2","FIELD1"]')
);

This works because your table already enforces STRICT WITH UNIQUE KEYS, so we don’t have to handle duplicate keys that could skew the array comparison.

Key Notes #

• Both approaches are valid, but Approach 1 is more efficient since it reuses your existing constraints.
• Oracle 12.2 fully supports json_keys, json_array_length, and json_equal in CHECK constraints—no workarounds needed.

内容的提问来源于stack exchange，提问作者bernhard.weingartner