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.
See my articles more often when you search Google.
