Two JSON documents can be the same data written differently: fields in another order, extra spaces, 1.0 instead of 1. Comparing them as text says they differ. JSON_EQUAL compares documents by content, so you can detect real changes, deduplicate documents, or check a result in a test, without being fooled by formatting.
Code for This Guide
The main example is in the examples/json folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
json_equal(expr1, expr2 [{true | false | error} on error])JSON_EQUAL is a condition: true when both documents have the same content, false otherwise. Use it in WHERE or CASE.
Compare by Content
The first query compares two documents whose fields are in a different order. The second computes a data guide of the loyalty documents with JSON_DATAGUIDE, an aggregate that lists every path with its type.
Example:
select case when json_equal('{"a":1,"b":[1,2]}', '{"b":[1,2],"a":1}')
then 'equal' end as same
from dual;
select json_dataguide(loyalty, dbms_json.format_flat, dbms_json.pretty) as dataguide
from customers;Output:
SAME
________
equal
DATAGUIDE
_____________________________________________
[
{
"o:path" : "$",
"type" : "object",
"o:length" : 1
},
{
"o:path" : "$.tier",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.points",
"type" : "number",
"o:length" : 4
},
{
"o:path" : "$.memberId",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.memberSince",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.preferences",
"type" : "object",
"o:length" : 1
},
{
"o:path" : "$.preferences.meal",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.preferences.seat",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.preferences.newsletter",
"type" : "boolean",
"o:length" : 4
},
{
"o:path" : "$.favoriteAirports",
"type" : "array",
"o:length" : 1
},
{
"o:path" : "$.favoriteAirports[*]",
"type" : "string",
"o:length" : 4
}
]What Counts as Equal
Field order and white space do not matter, and numbers compare by value. Array order and data types do matter.
Example:
select case when json_equal('{"a":1,"b":2}', '{ "b" : 2, "a" : 1 }') then 'equal' else 'different' end
as field_order,
case when json_equal('[1,2]', '[2,1]') then 'equal' else 'different' end
as array_order,
case when json_equal('{"a":1}', '{"a":1.0}') then 'equal' else 'different' end
as one_vs_one_point_zero,
case when json_equal('{"a":"1"}', '{"a":1}') then 'equal' else 'different' end
as string_vs_number
from dual;Output:
FIELD_ORDER ARRAY_ORDER ONE_VS_ONE_POINT_ZERO STRING_VS_NUMBER ______________ ______________ ________________________ ___________________ equal different equal different
| Difference | JSON_EQUAL |
|---|---|
| Field order, spacing, line breaks | Equal |
| 1 and 1.0 | Equal |
| Array elements in another order | Different |
| "1" (string) and 1 (number) | Different |
Things to Know
- Use JSON_EQUAL in a trigger or a MERGE to skip updates that would not change a document.
- With text columns, = compares the characters, so differently formatted documents are not equal. With values of the JSON data type, = also compares content; JSON_EQUAL works for both and states the intent clearly.
- To see how a set of documents is structured, rather than whether two are equal, use JSON_DATAGUIDE.
Related Guides
Conclusion
JSON_EQUAL compares two JSON documents by content: field order, formatting, and number notation are ignored, while array order and data types count. Use it whenever you need to know whether two documents hold the same data.
