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

17:00 UTC   Start Times Around the World

Description

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.

Highlights

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

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.

Your Experts

Beda Hammerschmidt
Beda Hammerschmidt, Consulting Member of Technical Staff    
Beda is a developer (and self-proclaimed developer advocate) in the JSON team. Ask him anything JSON.
Blaine Carter
Blaine Carter, Developer Advocate for Open Source    
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.

Questions

Question
Hello Beda, I have a question regarding keys having a "null" value in JSON. The JSON documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-data.html#GUID-FBC22D72-AA64-4B0A-92A2-837B32902E2C 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
Answer

Hi Iudith,

thanks for your question and sorry for my late reply.

We basi Show more.

Answered by Beda Hammerschmidt -