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%20the%20SQL%20Console%20for%20our%20HDI%20Container

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%20is%20active%2C%20and%20we%20have%2030%20Collections

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%20our%20first%20Collection

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%20using%20the%20JSON%20Interface

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%20using%20the%20SQL%20Interface

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;

Select%20*%20from%20our%20Collection

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%20*%20using%20a%20WHERE%20clause

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%20*%20using%20a%20Nested%20WHERE%20clause

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”)

Selecting%20*%20using%20a%20WHERE%20clause

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%20a%20JSON%20Array%20Document%20in%20HANA%20DocStore