Skip to Content
Product Information
Author's profile photo Laura Nevin

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:

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

 

Happy exploring!

Assigned tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Are there any plans to support the SAP HANA Cloud JSON Document Store in the SAP Cloud Application Programming  Model (CAP)?

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      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

      Author's profile photo Ovidiu Muntean
      Ovidiu Muntean

      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

      Author's profile photo Markus Fath
      Markus Fath

      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

      Author's profile photo Martin Stenzig
      Martin Stenzig

      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 ('{...}')

       

      Author's profile photo Markus Fath
      Markus Fath

      Hej Martin,

      sorry, I am not aware of a valid workaround.

      Regards, Markus

      Author's profile photo Andrew Beck
      Andrew Beck

      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?

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      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

      Author's profile photo Kevin Zhang
      Kevin Zhang

      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?

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      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

      Author's profile photo Kevin Zhang
      Kevin Zhang

      Hi Mathias,

      Thanks a lot.~~

      Cheers,
      Kevin

      Author's profile photo Antonio Maradiaga
      Antonio Maradiaga

      Are there any plans on enabling/supporting the JSON document store in HANA Cloud Trial?

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      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