Which is the suitable database for storing a large JSON?

Nikhil Chilwant picture Nikhil Chilwant · Sep 4, 2018 · Viewed 7.4k times · Source

I have only one large JSON file. For example,

{   
    "Name": "Motor_M23",
    "AASID": {
        "IDType": "URI",
        "IDSpec": "http://acplt.org/AAS/Motor_M23"
    },
    "AssetID": {
        "IDType": "URI",
        "IDSpec": "http://acplt.org/Assets/Motor_M23"
    },
    "Header": {
        "PropertyValueStatementContainers": [
            {
                "Name": "Config",
                        .
                        .
                        .
                        .

I need to support operations following operations:

  1. Querying for an element should return all child elements e.g. Querying for AssetID should return

    "AssetID": {
        "IDType": "URI",
        "IDSpec": "http://acplt.org/Assets/Motor_M23"
    }
    
  2. Update value of elements.

  3. Delete elements.
  4. Move elements between hierarchy levels e.g. make AssetID child element of AASID.

I considered following approaches:

  1. Graph database : I started reading about Neo4J. However, it can not create graph from JSON intellgently. One has to specify node type and their hierarchy order.
  2. ElasticSearch : It can work by treating JSON as text and hence not efficient solution.
  3. Postgres : It supports querying over JSON objects but updating, deletions won't be efficient.

Is there any good database out there which can load data from large JSON and handle my operations?

Answer

user3788685 picture user3788685 · Sep 15, 2018

If you are only working with JSON then you should really use a document oriented database as it will save you having to wrestle something sql related.

MongoDB is a good choice, supports many drivers and can deal with tree structures (Though I'm not sure about the automatic creation)

CRUD operations are simple and cover a wide range of cases.

For very large datasets on busy servers you should use the XFS file system and the WiredTiger storage engine as there are some gains in performance.

It's well supported, and isn't that much of a learning curve. (I came from Pure SQL without too much trouble)

You also have the option of MariaDB or MySQL which also both support JSON though I have no experience with either, and in the case of MySQL I feel it was just a 'bolt on' which had to be added in the face of an up-coming requirement.