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
Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Header container

January 08

17:00 UTC   Start Times Around the World

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 give us feedback and be notified of upcoming sessions!

Experts

Your Experts
Beda Hammerschmidt
Beda Hammerschmidt, Consulting Member of Technical Staff    
Beda is the brains and fingers behind XML and JSON support in Oracle Database.
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.

Answers

Answers
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 -