The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

  • December 5, 2017

Greater developer flexibility building JSON applications with Oracle NoSQL Database

Michael Schulman
Sr. Principal Product Manager

In this article, we look at how to go about storing and retrieving JSON data with Oracle NoSQL Database as our choice of the database because it further enhances developer flexibility.

If you are a developer building microservices for the web or mobile applications that communicate with other services over HTTP using REST (REpresentational State Transfer) architecture, then you are, usually working with JSON data. JSON has become the choice of developers for building microservices with its simplicity and flexibility. As you, or developers, started using JSON as the communication between microservices, writing and reading this JSON data to and from backend database became the need of the hour. Only because, that saved developers from converting (serializing/deserializing) JSON data fetched from the database as a result of a query run, thus further unleashing the power of JSON. Over the last few years, NoSQL databases have stepped up to provide comprehensive support for storing and querying JSON data, and relational databases have followed the trend.


Flexibility of JSON

JSON is a common data interchange format and provides flexibility to developers when it comes to designing and evolving data models for building microservices. It is useful for cases where data is unorganized. How to manipulate JSON data boils down to two aspects while developing applications.


  1. Modeling data to unleash  JSON flexibility

  2. Querying the free and wild JSON data effectively


It is important to note that databases store the JSON data in a manner which the record itself stores the type information.  Therefore each of the records is self-describing rather than schema-less. However, they do not have to adhere to any conventional structure within the scope of a table or record collection.

As an example, If we are building contacts database for Person entity, a sample JSON data could look like below -

"account" : 1,
"person" : {
      "lastName" : "Jones",
      "firstName" : "Joe",
      "address" : {
        "home" : {
          "street" : "15 Elm",
          "city" : "Lakeville",
          "zip" : "12345"
        "work" : {
          "street" : "12 Main",
          "city" : "Lakeville",
          "zip" : "12345"
      "phone" : {
        "home" : "800-555-1234",
       "work" : "877-123-4567"


Here, please note that there might be records of Persons for which the phone map is not available. Thus allowing the application to work with JSON data that conforms to various combinations of its object structure.

If the developer gets a new requirement that  “Person could have 2 or more phone numbers.”, He would be able to incorporate that use case with minimal code changes as opposed to the instance if he had to adhere to a standard structure for all records. Application developers can unleash this flexibility when they use JSON.

Before we go further, It is also important to note that - while the JSON data itself can have its structure, in all cases the context of information including the type (static or dynamic) of field is known to the application. In other words, Application still has the business context of data and knows how to manipulate the JSON data. It does not make any sense to store 2 JSON records that have entirely different structure and also, more importantly, different information context ( one record represents Person Contact, and another record represents Shopping Cart)  in one table or collection. The application (Object Mapping frameworks when used) brings in the type consistency to the free and wild JSON data format.

Now let's look at how does Oracle NoSQL Database extend this flexibility - for both data modeling and querying.

Data Modeling -

Oracle NoSQL database allows developers to manipulate data using the popular and well-understood paradigm of Tables. JSON is a native data-type for the column of this Table.  Meaning the Create Table DDL call can create JSON column types. So, let's see how this model allows for greater flexibility.

Let's consider following create table statement -

CREATE TABLE personContacts (
    account INTEGER,
    person JSON,
    Primary Key (account)

After statement execution, it creates a Table with primary key and JSON column. This model is equivalent to Document NoSQL stores such that each record in the table is a JSON document but with a primary (or shard) key defined outside.

You can assume that some of the properties of person JSON data are always defined (either by default or some value). It is possible to consider another way to define the same Table such as -

CREATE TABLE personContacts (
    account INTEGER,
    person JSON,
    primary_phone INTEGER,
    Primary Key (account)

Here, the table schema allows for self-described JSON type to be mixed with columns with other data types. Allowing for taking common fields or properties out of the JSON for the application to gain better control wherever applicable.

Querying -

Oracle NoSQL Database provides comprehensive ways to query individual fields or properties within the  JSON data type. Including the ability to create indexes on fields or properties from within the JSON data type.

Querying JSON is possible through API calls as well as the SQL-like interface. In this blog, we will look at some of the examples using the SQL-like interface, for APIs supported to perform queries over JSON data, please see this section in the product documentation.

In general, querying can be a topic of series of blog posts, but here I will discuss some of the high-level aspects that contribute towards the flexibility and in turn power of querying JSON data.

Oracle NoSQL Database supports Expressions that represent a set of operations using various operators (arithmetic, logical, value and sequence comparisons), function calls and other grammatical constructs performed over the records stored to produce a result.

Consider the query below -

sql-> select p.person.lastName, p.account from personContacts p where p.person.address.home.city = "Lakeville";



1 row returned


The query returns lastName and account number associated with all users that live in “Lakeville.” The expression steps into the JSON data to identify the lastName property. The example is a typical Select-From-Where query in which the where clause can use specific attributes from the JSON data as a predicate.

Such expressions can be written to form a variety of SFW (Select-From-Where) queries to retrieve results based on complex criteria or conditions.  They can be added at multiple places in the supported grammar further enhancing the flexibility. The supported grammar for SFW queries is below-

SELECT <expression>
FROM <table name>
[WHERE <expression>]
[ORDER BY <expression> [<sort order>]]
[OFFSET <number>]
[LIMIT <number>]; 

The beauty of the query support is that the same set of queries would work on complex data types such as Array, Map, and Record which are supported by Oracle NoSQL Database.  As a developer if you want to move from wild and free JSON to more structured schema approach by mapping JSON data to multiple column types, the queries will remain untouched. So as you firm up your application schema, you will have a choice to stay with JSON formatted data or move to a "type consistent" table schema.

Oracle NoSQL Database version 4.5 or higher will include support for JSON related features we discussed earlier; you can download this version from here. To learn more about building real-world web-scale JSON data applications with Oracle NoSQL Database, refer to this extensive tutorial.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.