Db2

 View Only

Db2 with JSON Capabilities

By Rohith Ravindranath posted Sun September 22, 2019 01:28 PM

  

Working with JSON Objects in Db2 

The emergence of NoSQL databases such as MongoDB that are document-oriented database programs has created a disturbance in the database space. Simply meaning, they store the data in JSON documents allowing organizations to store semi-structured and un-structured data more more easily, such as images, messaging data, and device data. 


What is a NoSql Document Database? 

NoSql Document Databases are based on a model that does not require SQL and tables. A normal relational database uses datatypes, columns, rows, schemas, and tabular relations. However, a NoSQL uses documents with data type descriptions and values. Groups of documents are called collections and have the same meaning as a table in relational database. The really important feature is that the documents within the collections can have the same or different structures depending on the user’s needs. 

Db2 with JSON Objects 

It is important that our products are able to adapt to the rapidly growing technology industry. Relational databases have been strictly SQL and were not able to handle JSON input and output. Db2 now comes with features to handle and store JSON documents in your relational database. Mostly importantly, one can have a table where only certain columns are stored as JSON objects. By adopting this feature, we are expanding our consumer base and opening our product to a new market. 

The best part about this new feature is that it can all be done in SQL. Creating a table with JSON object columns, inserting and selecting JSON objects can all be done with SQL statements. This way, we keep the power of Db2 as a relational database while still being competitive in the document-based database market.

Db2 with JSON Objects Demo


We have created a simple demo using Jupyter Notebook showcasing how to to integrate JSON objects into your Db2 database. In this notebook, we go over:

  1. Creating a Table with JSON objects as the datatype for certain columns
  2. Inserting data with normal data types and JSON objects 
  3. Selecting data with normal data types and JSON objects 


In our notebook, you will notice that when we are inserting our JSON data into to our table, we are converting the object to BSON. When you go to your Db2 instance and view the data, you will notice that the data for that column is in binary. This is what BSON is. So when we are selecting our data from the database, we will have to convert the BSON data into JSON so that we can view the data that is stored. This JSON to BSON and BSON to JSON is all done through system calls within the SQL statement. 

We have also given a video demo that shows the notebook in action and shows realtime updates to the Db2 on Cloud instance. That way we can see how the JSON data is being stored as BSON within the database. 

Inserting Data Into Our Table


In the picture below we can see we are inserting data with normal data types and also JSON objects. At first we are wrapping the data as a JSON object. We then are making a system call  ( SYSTOOLS.JSON2BSON() ) that converts the JSON object to BSON. This BSON object is what ultimately will be sent to the database to be stored. One important thing to note is that this is all one SQL statement.


img-5.png

Selecting Data From our Table


In the picture shown below, we demonstrate one way we can extract the JSON information. We know that the JSON object is stored in our database as a BSON. So in our select statement we want to convert it back to JSON using another system call ( SYSTOOLS.BSON2JSON() ), that way we are able to read the data.


img-6.png


However, suppose you don’t want to read the entire JSON object, rather only a specific value or attribute. For this purpose there is a system call ( JSON_VAL() ) that we can use that allows the user the specify which attribute within the JSON object to extract. 


img-7.png

Pre-requisites and Special Notes

The code samples that have been published on Github, have been tested only with Db2 on Cloud instances. We are running Python 3.7 and using Jupyter Notebooks 6.0.0.


When trying to connect to your DB2 instance, ensure that you are importing both “ibm_db” and “ibm_dbi.” The library “ibm_db” is a lower level library that directly communicates with the database, while “ibm_dbi” is an easy interface that communicates with the user and ibm_db to get the data you want. 


When you run !pip install ibm_db, it will install ibm_db and ibm_dbi.


In some cases, the command !pip install ibm_db will not work. In order to work around this, you have to have run Jupyter Notebook within a Docker container. 


If that doesn’t work follow the instructions in this link:  https://github.com/ibmdb/python-ibmdb/issues/276

Links

Github Link - https://github.com/IBM/db2-samples/tree/master/db2_with_json

YouTube Link - https://www.youtube.com/watch?v=Vr3-K5ffKvI

BSON Link - http://bsonspec.org
Db2 JSON Affiliated System Calls  -https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/json/src/tpc/db2z_jsonfunctions.html


#Db2
1 comment
24 views

Permalink

Comments

Fri October 04, 2019 01:48 PM

There's a free e-book (PDF) on using Db2 and JSON authored by George Baklarz.  Click ibm.biz/db2json