Product Information
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!
Are there any plans to support the SAP HANA Cloud JSON Document Store in the SAP Cloud Application Programming Model (CAP)?
Hi Mustafa,
support for CAP is part of our future roadmap. However, as of now there is not target release and we have not committed to a timeline.
Regards,
Mathias
Hi Mathias.
Do you know by any chance if there are any samples/blogs/documentation on how we can custom implement SAP HANA Cloud JSON DocStore in SAP CAP.
Thank you,
Ovidiu
Hej Ovidiu,
there is currently no documentation on how to use SAP CAP on HANA Cloud DocStore because we just started to work on CAP support. Stay tuned.
Regards, Markus
Markus,
is there a temporary workaround? I just tried to hook into the before create event, but don't seem to be able to influence the INSERT INTO statement enough to suppress the keys and only specify the value to result in INSERT INTO [Collection Name] VALUES ('{...}')
Hej Martin,
sorry, I am not aware of a valid workaround.
Regards, Markus
Very nice! I particularly like the ability to join between the doc store and regular column tables.
Is there a plan to include this in the on-premise Hana version?
Hi Andrew!
The Document Store is also available onprem with HANA2 SPS03 and above. However, the majority of new features will be developed for SAP HANA Cloud.
Regards,
Mathias
Is the HANA Document store a service in SCP? or a feature of HANA Cloud.
How is the pricing comparing to the hot memory store in HANA Cloud?
Hi Kevin,
the Document Store is a feature of HANA Cloud. It can be enabled for any instance with at least 3 vCPUs. This can be done with a check box during instance creation or via support ticket for existing instances. There is not separate charge for the Document Store.
Regards,
Mathias
Hi Mathias,
Thanks a lot.~~
Cheers,
Kevin
Are there any plans on enabling/supporting the JSON document store in HANA Cloud Trial?
Hi Antonio,
the Document Store requires at least 3 vCPUs and HC Trial only has 2. To may knowledge it is not planned to size HC Trial differently.
Regards,
Mathias