MariaDB NoSQL data using dynamic columns or JSON

Question

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?

Answer 1

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

Answer 2

I am wondering this same question! There isn't much information I can find on which data storage method to use for these use cases. Hopefully someone with some MariaDB expertise can enlighten us