Spotlight: SAP HANA Cloud JSON Document Store

The QRC 04/2020 release of SAP HANA Cloud debuts the long-awaited JSON Document Store feature that SAP HANA Platform (on-premise) customers have been enjoying.

When and why would I use this feature?

The JSON Document Store is a dedicated, fully integrated binary store for JSON documents in SAP HANA Cloud that provides all core database capabilities. With JSON Document Store, you can store any valid JSON document, and enjoy native database operations on JSON documents with seamless SQL integration.

Consider using the JSON Document Store when your application data is already in JSON format, or when there is a need to combine transactional data with JSON data.

JSON format is schema flexible; the data itself describes its own structure in arrays, objects, and key-value pairs. In fact, you might consider storing data in JSON format when schema changes are the norm or aren’t predictable. Storing data in JSON format offers advantages when the number of equivalent relational tables would be large but sparsely populated and would require multi-table cross joins.

What kind of DDL and DML support is offered?

The JSON Document Store allows you to use the SAP HANA database as both a relational and document-oriented database to store and query JSON documents using DDL statements.

Get JSON documents into and out of the database using the IMPORT/EXPORT and LOAD/UNLOAD statements. Then use common DDL and DML SQL statements such as INSERT, UPDATE, DELETE, SELECT, and CREATE HASH INDEX to select, modify, and index your JSON document data in the database.

You can also join JSON data with column store tables, and also query and update specific elements of your JSON documents using path expressions.

Since JSON Document Store transactions are fully ACID compliant, your JSON data can enjoy the same operational and administrative benefits as other relational database objects such as tables.

Examples

Here are a few examples to demonstrate some of the capabilities offered for the JSON Document Store:

 -- Create a collection
CREATE COLLECTION customers;

-- Insert data into a collection
INSERT INTO customers VALUES({"name": 'Paul',
                              "address": {
                                   "street": 'Hauptstrasse 10',
                                   "city": 'Heidelberg'},
                              "itemsSold": 3,
                              "active": True
                             });

-- Query to get a JSON string as result)
SELECT * FROM customers
       WHERE "address"."city" = 'Heidelberg';

-- Query to get a tabular result set
SELECT "name", "address"."street" FROM customers
       WHERE "address"."city" = 'Heidelberg';

-- Join with a column table
CREATE COLUMN TABLE t1 ("name" nvarchar(20), "age" int);
INSERT INTO t1 VALUES('Paul', 34);

WITH myView AS (SELECT "name", "address"."city" as "city" FROM customers)
       SELECT t."age", t."name", myView."city" 
         FROM myView INNER JOIN t1 AS t
         ON myView."name" = t."name";

-- Update individual fields
UPDATE customers SET "address"."city" = 'Mannheim'	
       WHERE "name" = 'Paul'

-- Compose new structure
SELECT { "firstName": "name", "city": "address"."city" }
         FROM customers;

-- Aggregation
SELECT "address"."city" AS city, COUNT(*) AS count
       FROM customers 
       GROUP BY "address"."city";

Where can I learn more about the JSON Document Store?

You can start here for more information: Getting Started with JSON Document Store

And also check out these videos on SAP HANA Academy‘s YouTube channel:

• SAP HANA Academy – SQL Functions: JSON_Query [2.0 SPS 00]
• SAP HANA Academy – SQL Functions: JSON_Table [2.0 SPS 00]

And finally, there are also some end-to-end blog posts on the SAP Community, as well:

• The SAP HANA JSON Document Store – Introduction (Part 1) (2018)
• The SAP HANA JSON Document Store – Use Case (Part 2) (2018)

Happy exploring!