Why (and How) You Should Manage JSON with SQL

What many people don’t know is that the best way to manage JSON is with a seemingly ancient tool: the SQL language.

Mar 16th, 2022 6:50am by Chris Saxon

Featued image for: Why (and How) You Should Manage JSON with SQL

Featured image via Pixabay

We all know that many modern apps rely on REST APIs to talk to other services, especially in the cloud. We all know those APIs usually send and receive data using a recent innovation: JavaScript Object Notation (JSON) documents. And we know there are a lot of methods for storing, managing and sharing these documents in your applications.

Chris Saxon

Chris is a developer advocate for Oracle Database, where his job is to help you get the best out of it and have fun with SQL. You can find him on Twitter, @ChrisRSaxon, and on his blog, All Things SQL.

What many people don’t know is that the best way to manage JSON is with a seemingly ancient tool: the SQL language. Let’s look at why that is and five examples of how to do it. Relational databases, and the SQL language that manipulates data inside them, were developed more than 40 years ago to solve data quality problems with the storage technologies of the day. Databases such as MySQL, Oracle Database, IBM DB2, Microsoft SQL Server and others became wildly successful in part because they saved developers and DBAs so much time compared to manual methods of dealing with duplicate, partial or missing records. These databases also brought strong transaction guarantees, making it easier for developers to ensure correct results in multiuser environments. Over those past four decades, the relational model and the SQL language have proven over and over again to be ideal tools for working with large amounts of data. Very large amounts of data. Huge. Massive. That’s where JSON comes in. JSON documents can be large and contain values spread across tables in your relational database. This can make creating and consuming these APIs challenging because you may need to combine data from several tables to form a response. However, when consuming a service API, you have the opposite problem, that is, splitting a large (aka massive) JSON document into appropriate tables. Using custom-written code to map these elements in the application tier is tedious. Such custom code, unless super-carefully constructed by someone who knows how databases work, can also lead to many roundtrips to the database service, slowing the application to a crawl and potentially consuming excess bandwidth. That’s terrible for a mobile app. A faster and easier way to handle JSON coming from service APIs is by using SQL. You can use only a few lines of SQL to better manage JSON documents in your application. Here are five quick examples:

Generate JSON with SQL.
Convert JSON to relational rows and columns.
Update JSON documents.
Search JSON efficiently.
See the structure of JSON documents.

Generate JSON with SQL

You’ve been tasked with producing an API. It must return department details and an array of the employees working in it. For example:

{

"department": "Accounting",
"employees": [
   {
      "name": "Higgins, Shelley",
      "hireDate": "2002-06-07T00:00:00"
   },
   {
      "name": "Gietz, William",
      "hireDate": "2002-06-07T00:00:00"
   }
 ]
}

It’s likely the department data and employee data are in separate tables. To create a JSON document within the application, you might first query the departments table and then fetch its employees, iterating through them to create the JSON array. This could lead to lots of calls to the database — and the network is usually the slowest part of the stack. Thus, each extra call wastes time better spent processing the data, or in providing the end user with a snappy experience. With SQL, you can create the whole document with one query:

select json_object (
      'department' value d.department_name,
      'employees' value json_arrayagg (
        json_object (
          'name' value last_name || ', ' || first_name,
          'hireDate' value hire_date
       )
     )
   )
from hr.departments d
join hr.employees e
on d.department_id = e.department_id
group by d.department_name;

This SQL code builds the whole JSON document in one trip to the database. Even if the data are spread across 10 tables, just add them to the join, and you can get everything in a single fetch. Returning the data as JSON saves you from having to map the columns to JSON fields within the application. This reduces the amount of code you need to write. It also decouples the application code from the database schema, making it easier for you to change table structures in the future. All you need to do is update the SQL code. As a final benefit, the structure of the JSON functions closely matches the schema of the JSON document, which makes it easier to see if the query returns the format needed. Generating JSON is only half the challenge. When consuming APIs, you also need to take JSON responses and store them in relational tables.

Convert JSON to Relational Rows and Columns

If you are programming, this is hard. But SQL makes this task simple. With a JSON_table, you can unnest JSON arrays, returning a row for each element within them. For example, by passing the document above as a bind variable to this query, SQL can generate a row for each employee:

select j.*
from json_table (
   :json_document,
   '$' columns (
      department path '$.department',
      nested path '$.employees[*]'
      columns (
         name path '$.name',
         hire_date path '$.hireDate'
   )
 )
) j;
DEPARTMENT  NAME            HIRE_DATE
Accounting  Gietz, William  2002-06-07T00:00:00
Accounting  Higgins, Shelley 2002-06-07 T00:00:00

You can insert the output of the query straight into your tables, avoiding the need for database mapping code and limiting roundtrips. At this point, you may be thinking: Wouldn’t it be easier to insert the document as is into a single table in the database? This would certainly make adding new documents and fetching them by primary key even easier. But it brings different challenges. You might still need to change existing documents. Searching stored JSON documents can be slow. And how exactly do you know what the structure of these documents are? Fortunately, Oracle Database has many options to help you work with JSON documents stored inside the database.

Updating JSON Documents

It’s likely you’ll need to change a JSON document after loading it into the database, such as when a customer updates information in a mobile app or if you added a new function to the API. Editing JSON documents can be hard, particularly if you need to make many changes to one document. So, while it’s straightforward to change a single value in the JSON document, such as a customer’s address, changing objects in an array gets tricky. With JSON_transform, you can add, remove or update many attributes using a single SQL statement. Here’s the SQL to remove the first entry in the employees array, add a new object to the end of that array and update the department name:

json_transform (
  :json_document, 
  remove '$.employees[0]',
  append '$.employees' = (
    json_object ( 
      'name'     value 'Saxon, Chris',
      'hireDate' value '2020-01-01T00:00:00'
    )
  ),
  replace '$.department' = 'Finance and Accounting'
)

The free-form nature of JSON documents means that attributes can be unexpectedly present, missing or null. To help you avoid unwanted changes or side effects, JSON_transform has clauses covering each of these cases. Each clause gives you the choice to ignore an anomaly or raise an exception. These options give you fine-grained control over the change. With SQL and JSON, combining the employee arrays from two departments into one array is easy. You may be wondering: How do you find these JSON documents?

Search JSON Efficiently

SQL’s simple dot-notation access lets you search JSON without much effort. All you need to do is pass the path to the attribute and the value you’re looking for. For example, this finds all the documents with a department attribute that has the value Finance:

select * from companies c
where c.department_data.department = 'Finance';

Of course, there could be millions of rows in the table, and real-world JSON documents can be large (or huge or massive). Scanning through all these rows can take a long time. This brings an important question: How do you make these queries fast? The key is to create indexes to support the query. Oracle Database has a range of ways to create indexes. Here are three:

JSON search index. This indexes the whole document.
Function-based indexes. These target one attribute in the document.
Multi-value indexes. These target many elements in an array.

Search indexes are best suited when you’ll have reporting tools or other ad-hoc queries accessing the data. They also enable the JSON Data Guide. Using this, you can get the schema of JSON stored in database tables. Multivalue and function-based indexes target specific attributes, which makes them smaller and more effective than search indexes. When you write JSON search queries in your app, it’s a good idea to build an index that matches the query’s where clause.

See the Structure of JSON Documents

The free-form nature of JSON is both its biggest strength and its biggest weakness. Once you start storing JSON documents in your database, it’s easy to lose track of what their structure is. The only way to know the structure of a document is to query its attributes. The JSON Data Guide is a function that solves this problem for you. By passing JSON documents to the `JSON_dataguide` function, you can get their schema. If you create a search index, this information becomes available in the database’s data dictionary, which includes the path and data type for every attribute stored in an indexed JSON document. For example, this query returns the structure of a JSON document stored in departments.department_json:

select json_dataguide ( 
         d.department_json, 
         dbms_json.format_hierarchical 
       ) 
from   departments d; 
  
{
  "type" : "object",
  "o:length" : 256,
  "properties" : {
    "employees" : {
      "type" : "array",
      "o:length" : 128,
      "o:preferred_column_name" : "employees",
      "items" : {
        "properties" : {
          "name" : {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "name"
          },
          "hireDate" : {
            "type" : "string",
            "o:length" : 32,
            "o:preferred_column_name" : "hireDate"
          }
        }
      }
    },
   "department" : {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "department"
    }
  }
}

Conclusion

Working with large JSON documents can be challenging for every application. No matter how the data is stored, it’s likely you need to map JSON to relational or vice-versa. Oracle SQL has many options to make this easy. This Live SQL script is an online lesson packed with examples showing how these features work. Check it out! For the full rundown on using SQL to manage JSON documents in Oracle Database, read the JSON Developer’s Guide. And if you want your own database to try these out on, sign up for Oracle Cloud Free Tier. You can also check out my free SQL classes on Oracle Dev Gym and my video combining SQL and magic on The Magic of SQL YouTube channel. I hope this article has helped you see why developers fell in love with SQL 40 years ago and how it can make you better at managing JSON docs today.