Skip to Content
Technical Articles
Author's profile photo Cameron Swift

SAP HANA Cloud Document Store – HANA DocStore First Steps


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",


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',


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 “” won’t work



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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Javier Andrés Cáceres Moreno
      Javier Andrés Cáceres Moreno


      Author's profile photo Patricia Rolon
      Patricia Rolon



      Nice Blog!!!

      Where can I find documentation about the privileges that are needed for this?

      It looks that, as the collections are tables cretaed in the SYSTEM schema, the users that will readd, insert, update and delete JSON will have to have privileges related to this schema. Is this correct?

      Thank you!


      Author's profile photo Mathias KEMETER
      Mathias KEMETER

      Hi Patricia, Collections are created within a schema (not restricted to SYSTEM). Just like tables. There are no privileges required, that are specific to the Document Store. From an authorization perspective Collections are treated like Tables.

      Author's profile photo Cameron Swift
      Cameron Swift
      Blog Post Author

      Thanks for reading Patricia - this is a great question, helpfully answered by my colleague Mathias. I've updated the blog post to be more clear about this

      Author's profile photo Patricia Rolon
      Patricia Rolon

      Hi Cameron,

      We are having an issue when storing JSONs in a collection using INSERT: the JSON gets truncated when its length is more than 1024 characters. Is this the limitation? If not (I hope so), how can this be solved?


      Thank you so much!

      Best Regards,



      Author's profile photo Mathias KEMETER
      Mathias KEMETER

      Hi Patricia, there is no such restriction for JSON documents in a collection. Depending if you are using HANA Cloud or on-premise, the limits can be checked in the documentation (cloud documentation, on-prem documentation). For HANA Cloud documents can have a size up to 2GB. It sounds like your JSON strings get truncated on the application layer before reaching the Document Store. Maybe there is an issue with the client or datatypes when passing the string?

      Author's profile photo Patricia Rolon
      Patricia Rolon

      You are right. We only had a problem with the viewer.



      Author's profile photo Patricia Rolon
      Patricia Rolon

      Hi Cameron, hi Mathias,


      Is it possible to create Odata accesses based on collections in the JSON document store?


      Thank you!



      Author's profile photo Mathias KEMETER
      Mathias KEMETER

      Hi Patricia, I do have limited knowledge about ODATA and CAP integration. I am assuming, that you can build a view on top of your collection and use this in a read-only fashion. However, I do not think that there is native support for collections.