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:
And finally, there are also some end-to-end blog posts on the
SAP Community, as well:
Happy exploring!
