Introduction

Within SAP HANA Cloud, we have the ability to activate the JSON Document Store (covered here). In this blog post, we’re going to walk through some of the technical details around how we interact with the SAP HANA DocStore

 

JSON Collections

While the Column and Row Stores use Tables to store data, the Document Store uses a JSON Collection

Just like Tables, our Collections are created within a given schema. There are no Document Store specific privileges required to work with Collections – only those already required to work with Tables in a given schema

When getting started with DocStore, we’ll be using the Database Explorer , but any method of passing SQL to HANA Cloud should be sufficient (we could, for example use the Business Application Studio instead)

Opening the SQL Console for our HDI Container from the right-click menu

 

First, we run the below statement to verify that DocStore is active in our SAP HANA Cloud tenant

SELECT object_count FROM m_feature_usage 
WHERE component_name = 'DOCSTORE' 
AND feature_name = 'COLLECTIONS';

 

If the result is NULL, then DocStore isn’t active in our tenant. If the result is a number, DocStore is active and we have a number of Collections equal to the result

 

DocStore is active, and we have 30 Collections

 

Creating a JSON Collection

We can create a JSON Collection with one line of SQL

 

CREATE COLLECTION myFirstCollection;

 

Creating our first Collection

 

Inserting a JSON Document

We have two different so-called Interfaces for inserting Documents into HANA Cloud DocStore Collections

Both of these Interfaces can be used through SQL

 

JSON Interface (JSON Syntax)

JSON Documents use double quotes around both Keys and Values. With the JSON Interface, we can import valid JSON Object Documents using SQL (split into lines for readability)

 

INSERT INTO MyFirstCollection VALUES ('{ 
	"name":"Peter Peterson",
	"address": {
		"street":"Faux St",
		"city":"Melbourne"
	}
}');

Inserting using the JSON Interface

 

Note that the values are entirely contained within brackets with single quotes

INSERT INTO Collection VALUES (' <JSON String Here> ');

 

SQL Interface (HANA SQL Syntax)

With HANA DocStore, the goal is to make JSON Documents a first-class citizen in SQL. For this reason, we can also insert a JSON Document using SQL Syntax

To insert a document using SQL Syntax, we can provide Keys enclosed in double quotes (or UPPERCASE without quotes), and Values enclosed in single quotes (split into lines for readability)

INSERT INTO MyFirstCollection VALUES ({ 
	"name":'Steven Stevenson',
	"address": {
		"street":'Falso St',
		"city":'Melbourne'
	}
});

Inserting using the SQL Interface

 

Note that the JSON Document within our round brackets isn’t entirely contained within single quotes. Keys use double quotes, and Values use single quotes

INSERT INTO Collection VALUES ({ "Keys":'Values' });

 

Selecting our JSON Documents

We can use SQL Select statements to retrieve Documents or Keys from a Collection

 

We can use Select * to retrieve all documents within the Collection

SELECT * FROM MyFirstCollection;

 

Selecting * from our Collection

 

If we want to retrieve the JSON Document for Peter Peterson, we can use a WHERE clause against the Key for name

SELECT * FROM MyFirstCollection WHERE "name" = 'Peter Peterson';

 

Selecting * using a WHERE clause

 

If we encounter any issues here, we can double check two things:

Check that our Key is written exactly as in the document (the WHERE is case-sensitive)

Check that our Value is inside single quotes – not double quotes

 

If we want to select against a nested key (such as city, inside address), we can use a full stop in our WHERE clause

SELECT * FROM MyFirstCollection WHERE "address"."city" = 'Melbourne';

 

Selecting * using a Nested WHERE clause

 

If we encounter issues here, we can check that each part of the WHERE clause key has its own pair of double quotes

WHERE “address”.”city” will work

WHERE “address.city” won’t work

 

Conclusion

Throughout this blog post we’ve demonstrated how to check that the Document Store is enabled, how to create a JSON Collection, how to insert JSON Documents and how we can select against our JSON Collection

I hope this blog post has been useful to you, and I welcome any comments or questions in the comments

For those already familiar with JSON Documents, I’ve included a brief note on JSON Arrays below

 

 

A Note on JSON Documents

Traditionally, JSON Documents can be either:

• A JSON Object (inside curly braces – { }) or
• A JSON Array (inside brackets – [ ])

HANA DocStore has been integrated with SQL, to allow for joins between JSON Documents and tables within Row and Column Stores. When we’re retrieving data from Documents, we retrieve values using keys (such as “name”)

Retrieving a JSON Document through SQL

 

For this reason, even though a JSON Array such as [0,1,2,3] is a valid JSON Document, we can’t directly store it in our HANA DocStore Collections because we don’t have a key to query against

In this case, we can use a JSON Object which contains our JSON Array, as long as the array has a key we can query against

Storing a JSON Array Document in HANA DocStore

 

Other Resources

 

SAP HANA Cloud Document Store – Enabling JSON Document Store by Cameron Swift

Spotlight: SAP HANA Cloud JSON Document Store by Laura Nevin

The Small JSON Document Store Cookbook by Mathias Kemeter

The SAP HANA JSON Document Store – Introduction (Part 1) by Kai Mueller

Ariba Analytics using SAP Analytics Cloud, Data Intelligence Cloud and HANA DocStore – Part 1 by Cameron Swift

 

Note: While I am an employee of SAP, any views/thoughts are my own, and do not necessarily reflect those of my employer