Skip to Content

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

Introduction to the Document Store (Part 1)


The SAP HANA JSON Document Store (also known as DocStore or Document Store) is a new feature which has been introduced with SAP HANA 2.0 SPS 01. The new store combines a relational and document-oriented database to a hybrid innovative technology which is unique for a variety of reasons, namely, its ACID compliant, fully integrated with SAP HANA in terms of access/query and administrative capabilities.

The embedded Document Store belongs to the group of “NoSQL” databases, more precisely to the document-oriented ones. These type of storing technologies are storing semi-structured documents (most JSON or XML) in collections without an explicit structure which offers high flexibility and compactness.

Beside the fact that the Document Store offers the possibility to use a document-oriented database directly and fully integrated in SAP HANA without the necessity of operating another independent database in parallel, it features full ACID properties. This way a single transaction may span all stores of SAP HANA and offer the same qualities in terms of atomicity, consistency, transaction isolation and durability. Given that the Document Store is a regular SAP HANA service, the known features Backup & Recovery, System Replication and Failover work out of the box without additional administrative overhead. SAP HANA allows interactions – especially joins – between collections and relational database objects like tables. Furthermore, with complex path expressions it’s possible to extract relevant portions of the document.


Beside the known terms like tables or schemas, this blog and the documentation of the Document Store uses some (new) terms which will be explained as follows:

Semi-structured data: Data which is not fixed in its structure but has the structure information in itself. In contrary, structured data like tables has a constant or fixed structure which must be defined before inserting data.

Collection: A collection holds multiple documents and is assigned to a schema. This is comparable to a table with the difference that a collection doesn’t have a predefined structure (column definition).

Document: A document in the Document Store is a semi-structured document in the JSON format. Such a document is like a row in a table. In this analogy the keys of the JSON document are the columns of the table.

Statement Examples

Since the Document Store is being used in relational database context, SQL is used as the query language. For that some new expressions and keywords where introduced to enrich SQL with the needs of the Document Store. In the following section the most commonly used statements are illustrated. This is only a simple statement explanation, for further detailed information kindly refer the SQL Reference.

Enablement of the Document Store

Since the document store is implemented as an additional store in SAP HANA that comes with its own process, it has to be enabled by the administrator in the SYSTEMDB for a specific tenant.

ALTER DATABASE <database> ADD 'docstore';

Create a collection

This statement creates a new collection called MyCollection into the current schema. This is like CREATE TABLE, but without defining the column characteristics. Users can create as many collections as needed.


Drop collection

By using the DROP COLLECTION statement, the whole collection will be deleted. This statement behaves like the known DROP statements.



The insert statement of the document store takes one JSON document as an argument without an optional column definition. The newly document must be valid JSON, but documents may have different identifiers or structure.

  "name":'John Doe',
  "address": {
    "city": 'Berlin',
    "street": 'Street 22' 


Selecting values from a collection is similar to the selection from a table. Furthermore, it is possible to access nested fields via a path by using the dot operator. The statement is tolerant to non-existing fields.

SELECT "name", "address"."city" AS "city" FROM MyCollection WHERE "name" = 'John Doe';

This returns a result set with the columns name and city where the name equals John Doe.


To perform updates on the data, the update statement should be used. Beside the simple updating of values, this operation can be used for adding or deleting field or for replacing whole documents.

UPDATE MyCollection SET "address"."city" = 'Munich' WHERE "name" = 'John Doe';


As the statement name implicates, it deletes documents from a collection.

DELETE FROM MyCollection WHERE "name" = 'John Doe';


SAP HANA already provides capabilities for graph, spatial, hierarchies and for relational tables of course. By introducing a document store the set of capabilities is enriched. This enables applications that are built on SAP HANA to use the best from each database technology. Especially they can mix different technologies with the well-known relational world in an intuitive way. This leads to many advantages, such as the ability to use a flexible and dynamic kind of storing data and the availability of using both database technologies at the same time. Overall it reduces administration overhead since only one database needs to be maintained and offers innovative development.


A short introduction into JSON

The Document Store in the SAP HANA Administration Guide

Maintenance of Collections in the SAP HANA Developer Guide (XS Advanced)

Document Store Statements in the SQL Reference

About this series

This blog series about the document store is splitted into two parts. In the first one, the document store is introduced with an overall overview.

In the second part a use case of the combination of relational and document oriented will be presented together with SQL samples.

About the authors

Bilal Shath and Kai Mueller are team members of SHOT, the SAP HANA Optimization Team. SHOT is part of SAP HANA Development and offers dedicated project-based and strategic SAP HANA support for ISVs and startups primarily in regard to functionality and performance.

You must be Logged on to comment or reply to a post.
  • Thanks Kai for the blog. Do i see some similarities of document store to mongoDB, if yes than will be great if you can highlight in a table what is same and what is different?  It will be easier for everyone to compare and understand.

    • Hello Nabheet,

      thank you for your comment. In my opinion it wouldn't make sense to compare the DocStore and MongoDB on this detail level, since the DocStore is part of SAP HANA and SAP HANA and MongoDB are complete different databases.

      In general your're right: they have the base idea in common. So, in both databases you have documents in the JSON format, CRUD operations to interact with them, collections which hold these documents and schemas/databases which storing collections.

      One big difference is the query language: DocStore uses SQL (with JSON extensions), MongoDB JavaScript/JSON. Also, MongoDB can handle XML documents. The DocStore has the possibility to interact directly with a relational database, which isn't supported by MongoDB.

      I hope this answers your question.

      Best regards, Kai

  • Thanks, Kai for the post.  I've briefly played with DocStore in HANA Express.  A quick question, would DocStore be an appropriate HANA DB Store to save large text file content?  (In my case, assume it's something like markdown text for a wiki application) Any sort of item length limitations I may run into?

    • Hello Mike,

      thank you for your nice words. Are you aware, that the SAP HANA JSON Document Store can save only JSON documents. If you want to save markdown, you need to convert or add it to a JSON document.

      Best regards, Kai

  • Hey Kai,

    Yes, I realize it must be in JSON format, however I'm more asking if there is a size limitation per-item.

    Example JSON item:

      "docTitle" : "somedocument",
      "content" : "Some potentially large markdown string"
  • Hi Kai,

    Very nice blog, thanks a lot for sharing.

    I have a question here, will SAP ABAP benefit from json document store? Can I combine standard SAP report with this feature?




    • Hello Season,

      thanks for the kind words. As far as I know you can't use the DocStore directly in ABAP since ABAP only knows tables as storage objects.

      But you can try to use collections and json in AMDPs with SQLScript as the language.

      Best regards,


  • Hi Kai,

    Thanks for this nice blog about Document Store (both Part1 and Part2).

    I tried using HANA Document store through Java App using JDBC(ngdbc.jar). I am able to perform CREATE COLLECTION and INSERT INTO Collection but not able to perform Select on collection as using ResultSet to get the value is not serving the purpose of getting JSON Data from collections.

    Is there any way to get data from Collection in Java (any API or Procedure) ?


    • Hello Amitanshu,

      JSON is returned as a CLOB in the first column, which you can convert to a String quite easy:

      try (Connection connection = DriverManager.getConnection(url, userName, password)) {
      	final String query = "SELECT * FROM COLL_A";
      	try (PreparedStatement statement = connection.prepareStatement(query)) {
      		try (ResultSet resultSet = statement.executeQuery()) {;
      private static String clobToString(final Clob clob) throws SQLException, IOException {
      	try (final Reader r = clob.getCharacterStream()) {
      		final StringBuffer buffer = new StringBuffer();
      		int ch;
      		while ((ch = != -1) {
      			buffer.append((char) ch);
      		return buffer.toString();

      Of course this will only convert this to a string, but then you can use well known JSON libraries to work further with this.

      I hope this helps.

      Best regards,


      • Thank Kai !! It's working perfectly. I was getting CLOB but was doing it using ResultSet metadata and hence it was not working as desired.  🙂


        Best Regards..


  • Hi Kai,

    We are wondering if SAP Hana DocStore can supports same level of data aggregation capabilities as MongoDB provides like aggregation pipelines or MapReduce or bulk operations. If yes, how can we achieve it?



    • Hello Chatar,

      you can use all SQL aggregation capabilities offered by SAP HANA, but there are no special ones for the SAP HANA DocStore.

      Best regards,


  • Hello Kai, I tried the following:

    INSERT INTO MyCollection1 VALUES ('{"BooleanField":true}');

    However, when I query using:
    SELECT * FROM MyCollection1 WHERE "BooleanField" = true

    it does not return any rows.

    How do we insert and query boolean and date fields in the doc store?


        • Hello Leonardo,

          I'm sorry but I'm not aware of any date which I can easily communicate externally, especially inside a blog.

          The tiering with NSE is a complete different story, so again as far as I know it's in the backlog.

          Best regards, Kai

  • Hi, Kai.  Have you ever created a Spring data repository for a HANA document store?  If so, can you describe it?  Extending JpaRepository doesn't seem to be a good fit.

    • Hello Joseph,

      I'm sorry but I never tried this and I don't know how this could work. What I guess is, that JPA/Spring etc. are not designed to work with JSON documents but I'm not an expert here.

      Maybe it could help if you figure out how to support other well known document-oriented databases in these frameworks and then try to adapt this for the DocStore.

      Best regards,


      • Thanks, Kai.  There is repository support in Spring for MongoDB (  I'll give it a try.

  • Kai,

    I did follow SAP Help documentation. I actually get below error in AMDOP class. However I am able to do all CRUD in SQL console on HANA Studio.

    “DEMOCOLLECTION” is unknown. ABAP objects and DDIC objects must be declared in the METHOD statement. Local names must start with “:” here.

    This error comes when accessing from system schema(example SAPCAR or SAPSLT etc) however works fine with user schema(example KDASS)


    Kevin Dass

    • Hello Kevin,

      I've seen you asked the same question also here. I guess the question is the more right place to ask. I'm not an ABAP expert but I guess you need to make the collection somehow visibel for ABAP, e.g. in the DDIC. Please follow up in the question since there somebody already figured out how to do that.

      Best regards, Kai

  • Hi Kai,

    We are trying to apply the doc store as a quick ingestion mechanism for the result of REST calls. Then later on parsing the json for the data which are needed in these particular use cases. I have a couple of questions:

    a) During insert if the JSON is invalid - then SQL error 8 is raised "Invalid Argument" - I cannot make an exit handler for this error code as it is not supported - are there any ways in which I can validate the json stream prior to saving.

    b) when using JSON_TABLE we are able to use nested paths for dealing with arrays in a "generic way" Is something similar when using the doc store directly.



    • Hi Michael,

      a) not that I know. HANA expects that the JSON you send is valid

      b) no, this is not possible but we are aware that this would be useful

      Best regards, Kai

  • I seem to be able to import single json documents  but not when its a array of json objects




    		"name": "rajarshi",
    		"lname": "muhuri",
    		"place": {
    			"city": "dallas",
    			"state": "tx"
    		"name": "rishi",
    		"lname": "muhuri",
    		"place": {
    			"city": "dallas",
    			"state": "tx"


    I can only insert when I break it into


    		"name": "rajarshi",
    		"lname": "muhuri",
    		"place": {
    			"city": "dallas",
    			"state": "tx"


    also for python insert , is this the only way ?

    x = 'json as string'


  • Hello Rajarshi,

    this is a normal SQL limitation. The SQL interface normally can only insert one row at once which applies to documents, too (as far as I know).

    Normally all clients support batch/bulk insert which can be used for documents too. Please refer to executemany here.

    Best regards,


    • Hi Kai ..


      thanks for clarifying

      For normal data, I move the data into tuples.

      but not sure how to do that for document store so that I can call executemany().

      currently I am doing this ,  

      for _l in _list:



  • Hi,

    you call executemany as you would do for a table

    con  = dbapi.connect(...)
    cur = con.cursor()
    docs = [{"a": 2}, {"b": 2}]
    params = [(json.dumps(doc),) for doc in docs]
    cur.executemany("INSERT INTO COL VALUES(?)", params)