Understanding JSON, OSON, and EXTENDED Mode in Oracle 26ai

Oracle Database 26ai continues to push JSON processing closer to native database semantics. One subtle but important area where this shows up is how extended JSON (for example, MongoDB-style $oid) is handled when converting text JSON into Oracle’s internal representation (OSON) and back again. 

In this post, we’ll walk through a simple SQL*Plus / SQLcl demo and explain: 

• What happens when JSON contains extended attributes like $oid
• The difference between json(:x) and json(:x EXTENDED)
• Why json_serialize(oson(...)) behaves the way it does

 

We start by storing a JSON document in a bind variable. Notice the _id field: it uses a MongoDB-style extended object with $oid

demo@ORA26AI> variable x varchar2(300)
demo@ORA26AI>
demo@ORA26AI> begin
  2    :x := q'# {
  3      "_id": {
  4        "$oid": "68d84c709ef2358f438a1cea"
  5      },
  6      "firstName": "fullName-ro",
  7      "type": "Prospect",
  8      "fullName": "fullName-ro",
  9      "birthDate": "1991-07-09",
 10      "nickname": "nickname-ro"
 11    } #';
 12    end;
 13*   /
 
PL/SQL procedure successfully completed.
 
demo@ORA26AI>
demo@ORA26AI> print x
 
X
--------------------------------------------------------------------------------
 {
    "_id": {
      "$oid": "68d84c709ef2358f438a1cea"
    },
    "firstName": "fullName-ro",
    "type": "Prospect",
    "fullName": "fullName-ro",
    "birthDate": "1991-07-09",
    "nickname": "nickname-ro"
  }
 

 

Now let’s parse the JSON using Oracle’s json() and oson() functions in EXTENDED mode.
 
demo@ORA26AI> select json_serialize( oson(:x extended) pretty ) ;
 
JSON_SERIALIZE(OSON(:XEXTENDED)PRETTY)
_________________________________________
{
  "_id" : "68d84c709ef2358f438a1cea",
  "firstName" : "fullName-ro",
  "type" : "Prospect",
  "fullName" : "fullName-ro",
  "birthDate" : "1991-07-09",
  "nickname" : "nickname-ro"
}
 
You see the same result if you directly call json constructors with extended options
 
demo@ORA26AI> select json_query( json(:x extended) ,'$' returning clob pretty) ;
 
JSON_QUERY(JSON(:XEXTENDED),'$'RETURNINGCLOBPRETTY)
______________________________________________________
{
  "_id" : "68d84c709ef2358f438a1cea",
  "firstName" : "fullName-ro",
  "type" : "Prospect",
  "fullName" : "fullName-ro",
  "birthDate" : "1991-07-09",
  "nickname" : "nickname-ro"
}
 
Now compare that with parsing the same JSON without EXTENDED mode:
 
demo@ORA26AI> select json(:x) ;
 
JSON(:X)
----------------------------------------
{
  "_id" :
  {
    "$oid" : "68d84c709ef2358f438a1cea"
  },
  "firstName" : "fullName-ro",
  "type" : "Prospect",
  "fullName" : "fullName-ro",
  "birthDate" : "1991-07-09",
  "nickname" : "nickname-ro"
}

Here, Oracle treats the document as pure JSON text:

• No special meaning is attached to $oid 
• The structure is preserved exactly as written
• _id remains a nested object

Key Differences at a Glance

Expression	$oid handling	Resulting _id
json(:x)	Not interpreted	Object:{"$oid":"..."}
json(:x EXTENDED)	Interpreted	Scalar string
json_serialize(oson(:x EXTENDED))	Interpreted + serialized	Scalar string

Understanding this behaviour is critical when: 

• Migrating JSON data from MongoDB or other document stores
• Designing JSON-relational duality views
• Ensuring consistent JSON shapes across applications 

If you expect _id to behave like a simple identifier, EXTENDED mode gives you a clean, normalized representation. If you need strict fidelity to the original JSON text, skip EXTENDED mode. 
 
Oracle 26ai gives you fine-grained control over how JSON is interpreted and stored. The choice between json(:x) and json(:x EXTENDED) is small syntactically—but it has a big semantic impact. When working with extended JSON, always be explicit about which behaviour you want. Your future self (and your applications) will thank you. 

In the next blogpost we will see about how these extended json types plays a major role in migrating json documents to relational tables.