Basic Queries

Because JSON is parsed and stored internally in native data formats with Oracle NoSQL Database, querying JSON data is no different than querying data in other column types. See Simple SELECT Queries and Working with complex data for introductory examples of how to form these queries.

In our JSONPersons example, all of the data for each person is contained in a column of type JSON called person. This data is presented as a JSON object, and mapped internally into a Map(JSON) type. You can query information in this column as you would query a Map of any other type. For example:

sql-> SELECT id, j.person.lastname, j.person.age FROM JSONPersons j;
 +----+---------------------+------------+
 | id |      lastname       |    age     |
 +----+---------------------+------------+
 |  3 | Morgan              | 38         |
 +----+---------------------+------------+
 |  2 | Anderson            | 35         |
 +----+---------------------+------------+
 |  5 | Scully              | 47         |
 +----+---------------------+------------+
 |  1 | Morrison            | 25         |
 +----+---------------------+------------+
 |  4 | Smith               | 38         |
 +----+---------------------+------------+
 |  6 | NULL                | NULL       |
 +----+---------------------+------------+
 |  7 | NULL                | NULL       |
 +----+---------------------+------------+

7 rows returned 

The last two rows in returned from this query contain all NULLs. This is because those rows were populated using JSON objects that are different than the objects used to populate the rest of the table. This capability of JSON is both a strength and a weakness. As a plus, you can modify your schema easily. However, if you are not careful, you can end up with tables containing dissimilar data in both large and small ways.

Because the JSON object is stored as a map, you can use normal map step functions on the column. For example:

sql-> SELECT id, j.person.expenses.keys($value > 1000) as Expenses 
from JSONPersons j;
+----+---------------------+
 | id |      Expenses       |
 +----+---------------------+
 |  3 | food                |
 +----+---------------------+
 |  2 | food                |
 |    | travel              |
 +----+---------------------+
 |  4 | clothes             |
 |    | food                |
 |    | shoes               |
 +----+---------------------+
 |  6 | NULL                |
 +----+---------------------+
 |  5 | clothes             |
 +----+---------------------+
 |  7 | NULL                |
 +----+---------------------+
 |  1 | NULL                |
 +----+---------------------+

7 rows returned 

Here, id 1 is NULL because that user had no expenses greater than $1000, while id 6 and 7 are NULL because they have no j.person.expenses field.