Part of Oracle JSON Insights, this post is going to take a fresh look at JSON Dataguide.
JSON offers applications valuable flexibility, but that flexibility can make it difficult to understand the structure of the data stored in a large document collection.
JSON Dataguide addresses this problem by automatically discovering JSON fields, paths, data types, and other structural metadata. Developers can use this information to explore evolving document models, generate relational views or virtual columns, and identify inconsistencies in their data.
In this post, we will dive into how JSON Data Guide works and how it helps you in your development day to day.
Overview
Let’s get started with a quick example showing how JSON Dataguide can be used to quickly discover the schema behind a JSON document in our Database.
drop table if exists movie;
create table movie (data json);
insert into movie (data) values
('{
"_id" : "6a6380c353050483dc3ee6ab",
"studio" : null,
"title" : "A Bag of Hammers",
"summary" : "A Bag of Hammers is a 2011 American comedy-drama film directed by Brian Crano and written by Crano and Jake Sandvig, who co-stars in the film with Jason Ritter. The soundtrack was written and performed by British folk musician Johnny Flynn.",
"sku" : "TEB2992",
"list_price" : 0,
"year" : 2011,
"awards" : null,
"runtime" : 85,
"gross" : null,
"cast" :
[
"Jason Ritter"
],
"movie_id" : 84,
"crew" :
[
{
"job" : "producer",
"names" :
[
"Lucy Barzun Donnelly"
]
}
],
"main_subject" : null,
"nominations" : null,
"budget" : 2000000,
"opening_date" : "2011-01-01",
"image_url" : "https://upload.wikimedia.org/wikipedia/en/4/44/A_Bag_of_Hammers.jpg",
"genre" :
[
"Comedy"
],
"wiki_article" : "A_Bag_of_Hammers",
"views" : 32
}
');
JSON Dataguide has several arguments to modify the generated output, but let’s go with the default values for now. Later in this post, we will go over those different options.
select json_serialize(JSON_DATAGUIDE(data) pretty) from movie;[
{
"o:path" : "$",
"type" : "object",
"o:length" : 1
},
{
"o:path" : "$._id",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.sku",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.cast",
"type" : "array",
"o:length" : 1
},
{
"o:path" : "$.cast[*]",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.crew",
"type" : "array",
"o:length" : 1
},
{
"o:path" : "$.crew.job",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.crew.names",
"type" : "array",
"o:length" : 1
},
{
"o:path" : "$.crew.names[*]",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.year",
"type" : "number",
"o:length" : 4
},
{
"o:path" : "$.genre",
"type" : "array",
"o:length" : 1
},
{
"o:path" : "$.genre[*]",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.gross",
"type" : "null",
"o:length" : 1
},
{
"o:path" : "$.title",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.views",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.awards",
"type" : "null",
"o:length" : 1
},
{
"o:path" : "$.budget",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.studio",
"type" : "null",
"o:length" : 1
},
{
"o:path" : "$.runtime",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.summary",
"type" : "string",
"o:length" : 256
},
{
"o:path" : "$.movie_id",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.image_url",
"type" : "string",
"o:length" : 128
},
{
"o:path" : "$.list_price",
"type" : "number",
"o:length" : 1
},
{
"o:path" : "$.nominations",
"type" : "null",
"o:length" : 1
},
{
"o:path" : "$.main_subject",
"type" : "null",
"o:length" : 1
},
{
"o:path" : "$.opening_date",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.wiki_article",
"type" : "string",
"o:length" : 16
}
]As can be seen, the output is a JSON document that describes the contents of the stored data. This can be applied on a JSON type column, a column storing JSON text, or JSON input. It will list all the paths found in the document, along with their type and length.
JSON Dataguide formats
There are three supported formats that can be generated by JSON Dataguide:
- Flat, which allows to quickly view all the fields in a simple list manner. This is the default format and is the one used in the example in the Overview section.
- Hierarchical, which shows the hierarchy of the fields within the JSON document.
- Schema, which also follows the hierarchy present within the JSON document, but is useful to enforce a JSON Schema.
Examples:
-- flat format
select JSON_DATAGUIDE(data, DBMS_JSON.FORMAT_FLAT) from movie;
-- hierarchical format
select JSON_DATAGUIDE(data, DBMS_JSON.FORMAT_HIERARCHICAL) from movie;
-- schema format
select JSON_DATAGUIDE(data, DBMS_JSON.FORMAT_SCHEMA) from movie;Enforcing JSON Schema
We will now show how we can use the JSON Dataguide feature to enforce JSON Schema on a table containing JSON data.
As you might already know, JSON is very useful for agile application development because it allows developers to get started without having to define a database schema beforehand. After a few iterations, once some data already exists in the database, a developer may have reached a degree of convergence in the shape of the JSON data being stored. At this point, it might be important to ensure that documents have a fixed shape in order to introduce indexing, improve storage, or partition their data.
Let’s get started, then. For this part, we will be using the movies data set shown in many of the Livelabs on livelabs.oracle.com.
drop table movie;
create table movie (data json);
insert into movie(data)
select *
from external (
(
data json
)
type ORACLE_BIGDATA
access parameters
(
com.oracle.bigdata.fileformat = jsondoc
)
location (
'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_landing/o/movie/*.json'
)
);
Then, we get the schema format Dataguide and use it to add a constraint to our table.
select JSON_DATAGUIDE(data, DBMS_JSON.FORMAT_SCHEMA) from movie;
alter table movie add constraint movie_schema
CHECK(
data is json
validate '{"type":"object","o:length":1,"properties":{"sku":{"type":"string","o:length":8,"o:preferred_column_name":"sku"},"cast":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"cast"},{"type":"array","o:length":1,"o:preferred_column_name":"cast","items":{"type":"string","o:length":64,"o:preferred_column_name":"scalar_string"}}]},"crew":{"type":"array","o:length":1,"o:preferred_column_name":"crew","items":{"properties":{"job":{"type":"string","o:length":32,"o:preferred_column_name":"job"},"names":{"type":"array","o:length":1,"o:preferred_column_name":"names","items":{"type":"string","o:length":64,"o:preferred_column_name":"scalar_string"}}}}},"year":{"type":"number","o:length":4,"o:preferred_column_name":"year"},"genre":{"type":"array","o:length":1,"o:preferred_column_name":"genre","items":{"type":"string","o:length":16,"o:preferred_column_name":"scalar_string"}},"gross":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"gross"},{"type":"number","o:length":16,"o:preferred_column_name":"gross"},{"type":"string","o:length":16,"o:preferred_column_name":"gross"}]},"title":{"type":"string","o:length":128,"o:preferred_column_name":"title"},"views":{"type":"number","o:length":4,"o:preferred_column_name":"views"},"awards":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"awards"},{"type":"array","o:length":1,"o:preferred_column_name":"awards","items":{"type":"string","o:length":128,"o:preferred_column_name":"scalar_string"}}]},"budget":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"budget"},{"type":"number","o:length":16,"o:preferred_column_name":"budget"},{"type":"string","o:length":16,"o:preferred_column_name":"budget"}]},"studio":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"studio"},{"type":"array","o:length":1,"o:preferred_column_name":"studio","items":{"type":"string","o:length":128,"o:preferred_column_name":"scalar_string"}}]},"runtime":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"runtime"},{"type":"number","o:length":4,"o:preferred_column_name":"runtime"},{"type":"string","o:length":8,"o:preferred_column_name":"runtime"}]},"summary":{"type":"string","o:length":4096,"o:preferred_column_name":"summary"},"movie_id":{"type":"number","o:length":4,"o:preferred_column_name":"movie_id"},"image_url":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"image_url"},{"type":"string","o:length":256,"o:preferred_column_name":"image_url"}]},"list_price":{"type":"number","o:length":4,"o:preferred_column_name":"list_price"},"nominations":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"nominations"},{"type":"array","o:length":1,"o:preferred_column_name":"nominations","items":{"type":"string","o:length":128,"o:preferred_column_name":"scalar_string"}}]},"kid_friendly":{"type":"string","o:length":4,"o:preferred_column_name":"kid_friendly"},"main_subject":{"oneOf":[{"type":"null","o:length":1,"o:preferred_column_name":"main_subject"},{"type":"string","o:length":64,"o:preferred_column_name":"main_subject"}]},"opening_date":{"type":"string","o:length":16,"o:preferred_column_name":"opening_date"},"wiki_article":{"type":"string","o:length":128,"o:preferred_column_name":"wiki_article"}}}'
) precheck;
This will now make sure only JSON documents that comply with the schema are allowed into the table.
insert into movie values ('[]');
ORA-40875: JSON schema validation error
JZN-00501: JSON schema validation failed JSON Dataguide with field statistics
JSON Dataguide has an option to generate statistics, which is the DBMS_JSON.GATHER_STATS flag as third argument.
In the following example, you can see how we use that along with the flag DBMS_JSON.PRETTY to pretty print the output.
select json_dataguide(data,
DBMS_JSON.FORMAT_FLAT,
DBMS_JSON.gather_stats + DBMS_JSON.PRETTY) from movie;[
{
"o:path" : "$",
"type" : "object",
"o:length" : 1,
"o:frequency" : 100,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.sku",
"type" : "string",
"o:length" : 8,
"o:frequency" : 100,
"o:low_value" : "44124",
"o:high_value" : "ZZX88067",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.cast",
"type" : "null",
"o:length" : 1,
"o:frequency" : 18,
"o:low_value" : null,
"o:high_value" : null,
"o:num_nulls" : 684,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.cast",
"type" : "array",
"o:length" : 1,
"o:frequency" : 82,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.cast[*]",
"type" : "string",
"o:length" : 64,
"o:frequency" : 82,
"o:low_value" : "\"Weird Al\" Yankovic",
"o:high_value" : "Željko Ivanek",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.crew",
"type" : "array",
"o:length" : 1,
"o:frequency" : 100,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.crew.job",
"type" : "string",
"o:length" : 32,
"o:frequency" : 89.29,
"o:low_value" : "director",
"o:high_value" : "screenwriter",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.crew.names",
"type" : "array",
"o:length" : 1,
"o:frequency" : 89.29,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.crew.names[*]",
"type" : "string",
"o:length" : 64,
"o:frequency" : 89.29,
"o:low_value" : "A. B. Guthrie Jr.",
"o:high_value" : "Łukasz Karwowski",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.year",
"type" : "number",
"o:length" : 4,
"o:frequency" : 100,
"o:low_value" : 1950,
"o:high_value" : 2021,
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.genre",
"type" : "array",
"o:length" : 1,
"o:frequency" : 100,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.genre[*]",
"type" : "string",
"o:length" : 16,
"o:frequency" : 100,
"o:low_value" : "Action",
"o:high_value" : "Western",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.gross",
"type" : "json(scalar)",
"o:length" : 16,
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "",
"o:num_nulls" : 3,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.title",
"type" : "string",
"o:length" : 128,
"o:frequency" : 100,
"o:low_value" : "\"Crocodile\" Dundee",
"o:high_value" : "Zootopia",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.views",
"type" : "number",
"o:length" : 4,
"o:frequency" : 100,
"o:low_value" : 1,
"o:high_value" : 13377,
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.awards",
"type" : "null",
"o:length" : 1,
"o:frequency" : 86.29,
"o:low_value" : null,
"o:high_value" : null,
"o:num_nulls" : 3279,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.awards",
"type" : "array",
"o:length" : 1,
"o:frequency" : 13.71,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.awards[*]",
"type" : "string",
"o:length" : 128,
"o:frequency" : 13.71,
"o:low_value" : "AACTA Award for Best Costume Design",
"o:high_value" : "audience award",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.budget",
"type" : "json(scalar)",
"o:length" : 16,
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "",
"o:num_nulls" : 1,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.studio",
"type" : "null",
"o:length" : 1,
"o:frequency" : 62.08,
"o:low_value" : null,
"o:high_value" : null,
"o:num_nulls" : 2359,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.studio",
"type" : "array",
"o:length" : 1,
"o:frequency" : 37.92,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.studio[*]",
"type" : "string",
"o:length" : 128,
"o:frequency" : 37.92,
"o:low_value" : "1492 Pictures",
"o:high_value" : "Österreichischer Rundfunk",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.runtime",
"type" : "json(scalar)",
"o:length" : 8,
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.summary",
"type" : "string",
"o:length" : 4096,
"o:frequency" : 100,
"o:low_value" : " Night Moves is a 2013 American drama thriller film directed by Kelly Reichardt and written by Reichardt and Jonathan Raymond, starring Jesse Eisenberg, Dakota Fanning, Peter Sarsgaard, Alia Shawkat, and James LeGros. The film follows three radical environmentalists who plot to blow up a dam. It was shown in the main competition section of the 70th Venice International Film Festival, at the 2013 Toronto International Film Festival and at 2013 Deauville American Film Festival, where it won Grand Prix of the festival.",
"o:high_value" : "ivansxtc is an American independent drama film co-written by Bernard Rose and Lisa Enos, produced by Lisa Enos and directed by Bernard Rose, the first of several Enos-Rose collaborations, including Snuff-Movie (2005), Kreutzer Sonata (2008) and Mr. Nice (2010). The film stars Danny Huston, Peter Weller, and Lisa Enos, with Rose and Enos' actual CAA agent, Adam Krentzmen, playing the role of fictional \"Media Talent Agency\" agent Barry Oaks. Other key roles include Morgan Walsh (Vukovic) as Lucy Lawrence, and SLC Punk director James Merendino in the role of director Danny McTeague. The story follows a Hollywood agent, Ivan Beckman (Danny Huston), who must force a smile and carry on with business as usual with the agency's biggest client, Don West (Peter Weller), in the face of a cancer diagnosis. The film, loosely based on Leo Tolstoy's 1886 novella The Death of Ivan Ilyich, was also inspired by the rise and fall of talent agent Jay Moloney. It premiered at the Toronto International Fil",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.movie_id",
"type" : "number",
"o:length" : 4,
"o:frequency" : 100,
"o:low_value" : 1,
"o:high_value" : 4003,
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.image_url",
"type" : "json(scalar)",
"o:length" : 256,
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.list_price",
"type" : "number",
"o:length" : 4,
"o:frequency" : 100,
"o:low_value" : 0,
"o:high_value" : 4.99,
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.nominations",
"type" : "null",
"o:length" : 1,
"o:frequency" : 81.21,
"o:low_value" : null,
"o:high_value" : null,
"o:num_nulls" : 3086,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.nominations",
"type" : "array",
"o:length" : 1,
"o:frequency" : 18.79,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.nominations[*]",
"type" : "string",
"o:length" : 128,
"o:frequency" : 18.79,
"o:low_value" : "2003 MTV Movie Awards",
"o:high_value" : "ZZZZZ",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.kid_friendly",
"type" : "string",
"o:length" : 4,
"o:frequency" : 1.53,
"o:low_value" : "true",
"o:high_value" : "true",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.main_subject",
"type" : "json(scalar)",
"o:length" : 64,
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "",
"o:num_nulls" : 7,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.opening_date",
"type" : "string",
"o:length" : 16,
"o:frequency" : 100,
"o:low_value" : "1950-01-01",
"o:high_value" : "2021-03-04",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
},
{
"o:path" : "$.wiki_article",
"type" : "string",
"o:length" : 128,
"o:frequency" : 100,
"o:low_value" : "'Gator_Bait_II:_Cajun_Justice",
"o:high_value" : "Zootopia",
"o:num_nulls" : 0,
"o:last_analyzed" : "2026-07-24T19:09:29",
"o:sample_size" : 3800
}
]DBMS_JSON package utilities
These DBMS_JSON PL/SQL package provides the following JSON Dataguide features, which rely on the hierarchical format:
- DBMS_JSON.CREATE_VIEW
- DBMS_JSON.ADD_VIRTUAL_COLUMNS
-- create view
declare
dg CLOB;
begin
select json_dataguide(data, dbms_json.FORMAT_HIERARCHICAL) into dg from movie;
dbms_json.CREATE_VIEW('MOVIES_V','MOVIE', 'DATA', dg);
end;
/
describe MOVIES_V;select distinct "year" from MOVIES_V order by 1;declare
dg CLOB;
begin
select json_dataguide(data, dbms_json.FORMAT_HIERARCHICAL) into dg from movie;
dbms_json.ADD_VIRTUAL_COLUMNS('MOVIE', 'DATA', dg);
end;
/
describe MOVIE;select count(*), "year" from movie group by "year" order by "year";Using JSON Dataguide in the context of AI
In AI systems, JSON is present all over the place because it is used as a common language among agents, tools, APIs, MCP servers, and memory agents. Based on this, JSON Dataguide can provide insight and understanding of those communications in order to further enhance AI systems.
Let’s take as an example a given MCP server we created for the purpose of creating, cloning, and renaming files. Upon logging all the messages received and produced by this MCP server when interacting with an AI agent, we see JSON documents like this:
{
"timestamp" : "2026-08-11T17:50:19.780520+00:00",
"direction" : "agent_to_server",
"message" : "{\"jsonrpc\":\"2.0\",\"id\":1,\"method\":\"tools/list\",\"params\":{\"_meta\":{\"progressToken\":0}}}",
"json" :
{
"jsonrpc" : "2.0",
"id" : 1,
"method" : "tools/list",
"params" :
{
"_meta" :
{
"progressToken" : 0
}
}
}
}If we use JSON_Dataguide, we can see the following structure
[
{
"o:path" : "$",
"type" : "object"
},
{
"o:path" : "$.json",
"type" : "object"
},
{
"o:path" : "$.json.id",
"type" : "number"
},
{
"o:path" : "$.json.result",
"type" : "object"
},
{
"o:path" : "$.json.result.tools",
"type" : "array"
},
{
"o:path" : "$.json.result.tools.name",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.tools.description",
"type" : "string",
"o:length" : 128
},
{
"o:path" : "$.json.result.tools.inputSchema",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.title",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.json.result.tools.inputSchema.required",
"type" : "array"
},
{
"o:path" : "$.json.result.tools.inputSchema.required[*]",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.tools.inputSchema.properties",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.path",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.path.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.path.title",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.content",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.content.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.content.title",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.content.default",
"type" : "string",
"o:length" : 1
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.new_name",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.new_name.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.new_name.title",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.backup_dir",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.backup_dir.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.backup_dir.title",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.backup_dir.default",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.destination",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.destination.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.inputSchema.properties.destination.title",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.tools.outputSchema",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.outputSchema.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.tools.outputSchema.title",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.json.result.tools.outputSchema.additionalProperties",
"type" : "object"
},
{
"o:path" : "$.json.result.tools.outputSchema.additionalProperties.type",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.content",
"type" : "array"
},
{
"o:path" : "$.json.result.content.text",
"type" : "string",
"o:length" : 128
},
{
"o:path" : "$.json.result.content.type",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.json.result.isError",
"type" : "boolean"
},
{
"o:path" : "$.json.result.serverInfo",
"type" : "object"
},
{
"o:path" : "$.json.result.serverInfo.name",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.serverInfo.version",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.json.result.capabilities",
"type" : "object"
},
{
"o:path" : "$.json.result.capabilities.tools",
"type" : "object"
},
{
"o:path" : "$.json.result.capabilities.tools.listChanged",
"type" : "boolean"
},
{
"o:path" : "$.json.result.capabilities.prompts",
"type" : "object"
},
{
"o:path" : "$.json.result.capabilities.prompts.listChanged",
"type" : "boolean"
},
{
"o:path" : "$.json.result.capabilities.resources",
"type" : "object"
},
{
"o:path" : "$.json.result.capabilities.resources.subscribe",
"type" : "boolean"
},
{
"o:path" : "$.json.result.capabilities.resources.listChanged",
"type" : "boolean"
},
{
"o:path" : "$.json.result.capabilities.experimental",
"type" : "object"
},
{
"o:path" : "$.json.result.protocolVersion",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.result.structuredContent",
"type" : "object"
},
{
"o:path" : "$.json.result.structuredContent.ok",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.json.result.structuredContent.path",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.json.result.structuredContent.action",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.json.jsonrpc",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.message",
"type" : "string",
"o:length" : 4096
},
{
"o:path" : "$.direction",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.timestamp",
"type" : "string",
"o:length" : 32
}
]We find out that we have a “direction” field, which could help categorize the messages we logged.
select m.data.direction from mcp_logger m;Using the DBMS_JSON package, we could create two views, one for the incoming messages and one for the outgoing ones.
declare
dg_in CLOB;
dg_out CLOB;
begin
select json_dataguide(data, dbms_json.FORMAT_HIERARCHICAL) into dg_in from mcp_logger m where m.data.direction.string() = 'agent_to_server';
select json_dataguide(data, dbms_json.FORMAT_HIERARCHICAL) into dg_out from mcp_logger m where m.data.direction.string() = 'server_to_agent';
dbms_json.CREATE_VIEW('MCP_LOGGER_IN','MCP_LOGGER', 'DATA', dg_in);
dbms_json.CREATE_VIEW('MCP_LOGGER_OUT','MCP_LOGGER', 'DATA', dg_out);
end;
/describe MCP_LOGGER_IN;describe MCP_LOGGER_OUT;Conclusions
JSON Dataguide is a great discovery tool that helps provide insight into your JSON documents.
You can benefit from JSON Dataguide’s capabilities to
- Discover the structure of your documents.
- Generate virtual columns, indexes, and views from JSON data.
- Understand the contents of your documents when running statistics.
- Enforce JSON Schema.
- Do all of the above on AI generated JSON data.
Oracle JSON Insights
Learn from the team that build the features at Oracle. Read articles like this post, watch demos and webcasts, or start to develop using the LiveLabs, all on available from one page: Oracle JSON Insights.