Skip to Main Content

JSON in Oracle Database Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

January 08, 2019

17:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


The JSON Search Index
Learn how to index JSON data in Oracle database with the JSON Search index and understand when to use it and how to tune it.


0.40: Recap on best practices on JSON storage and functional indexing.
1:20 why do I need the JSON Search index, examples and explanations
9:40 Full text search on text fields inside the JSON
12:50 Index syncing and what you need to know
17:00 Index-all, fanout and options to reduce the index’s size.
30:00 Discussion and questions from attendees

Your Experts

    Beda Hammerschmidt

    Beda Hammerschmidt   

    Beda is a developer (and self-proclaimed developer advocate) in the JSON team. Ask him anything JSON.
    Blaine Carter

    Blaine Carter   

    Blaine started programming in 1995. For most of his career, he used Oracle software to build applications, including Oracle Forms and Oracle Reports, Java, and a whole lot of SQL and PL/SQL. Now he applies his exploratory eye and tinkering inclinations to the intersection of open source software and Oracle Database.


Hello Beda, I have a question regarding keys having a "null" value in JSON. The JSON documentation: says the following: Note: A JSON value of null is a value as far as SQL is concerned. It is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In particular, SQL condition IS NULL returns false for a JSON null value, and SQL condition IS NOT NULL returns true. However, the following simple example seems to contradict this: create table t (id number, json_col varchar(32767) check (json_col is json)) / insert into t values (1, '{"Key1":"A", "Key2":null}') / commit / Both the following queries use an "IS NULL" predicate and they both return the record: select * from t mytab where mytab.json_col.Key2 is null / ID JSON_COL ---------------- 1 { "Key1" : "A", "Key2" : null } select * from ( select t.*, json_value(json_col, '$.Key2') val from t ) where val is null / ID JSON_COL VAL --------------------------- 1 { - "Key1" : "A", "Key2" : null } Can you please clarify this issue ? Thanks a lot & Best Regards, Iudith Mentzel

Hi Iudith,

thanks for your question and sorry for my late reply.

We basi Show more.

Answered by Beda Hammerschmidt -