Search code examples
jsonnosqlmariadbdynamic-columns

MariaDB NoSQL data using dynamic columns or JSON

I want to store dynamic statistical data in a NoSQL way - a structured document and be able to work on a parameter basis.

e.g.

{
  'client': 1245,
  'amount': 25425,
  'create_date': '2019-01-01'
}

As I understand, MariaDB has two different approaches since version 10.2:

  • using BLOB column data type and dynamic column functions COLUMN_CREATE, COLUMN_GET, COLUMN_LIST, ...
  • using VARCHAR column and JSON specific functions JSON_EXISTS, JSON_QUERY, ...

I see some pros and cons in both ways:

  • Dynamic columns
    • pros
      • seems faster
    • cons
      • less universal
      • less standard
      • suited more for one depth of parameters
  • JSON
    • pros
      • better in complex structure
      • more standard when exporting
      • easier data creation
    • cons
      • seems slower and less robust

I also feel the development of the JSON approach is more active in recent years.

Since I need to decide which way to go for the next couple of years and I haven't found any comparison of the approaches can someone shed some light on this topic?

Solution

  • There are no comments on my topic, so I can at least say what we decided for and why.

    We went the way of JSON because of these reasons:

    • we do not operate large scale data (max 100,000 records), so the speed is not quite an issue here (I did not have to make benchmarks)
    • there is definitely easier export of data and than easier analysis with available tools
    • there are more ways to alter structural data in JSON format
    • there is better readability of data using visual database tools
    • there is easier data serialization between app and data layer
    • finally, my personal feeling there is more active development on JSON side