Hi all, I am trying to decide on a database for time-series data. The data could be tracking some simple series like statistics over time or could be a nested JSON (multi-level nested). I have been experimenting with InfluxDB for the former case of a simple list of variables over time. The continuous queries are powerful too. But for the latter case, where InfluxDB requires to flatten out a nested JSON before saving it into the database the complexity arises. The nested JSON could be objects or a list of objects and objects under objects in which a complete flattening doesn't leave the data in a state for the queries I'm thinking.

[ 
  { "timestamp": "2021-09-06T12:51:00Z",
    "name": "Name1",
    "books": [
        { "title": "Book1", "page": 100 },
        { "title": "Book2", "page": 280 },
    ]
  },
 { "timestamp": "2021-09-06T12:52:00Z",
   "name": "Name2",
   "books": [
       { "title": "Book1", "page": 320},
       { "title": "Book2", "page": 530 },
       { "title": "Book3", "page": 150 },
   ]
 }
]

Sample query: With a time range, for name xyz, find all the book title for which # of page < 400.

If I flatten it completely, it will result in fields like books_0_title, books_0_page, books_1_title, books_1_page, ... And by losing the nested context it will be hard to return one field (title) where some condition for another field (page) satisfies.

Appreciate any suggestions. Even a piece of generic advice on handling the time-series and choosing the database is welcome!

We are building an IOT service with heavy write throughput and fewer reads (we need downsampling records). We prefer to have good reliability when comes to data and prefer to have data retention based on policies.

So, we are looking for what is the best underlying DB for ingesting a lot of data and do queries easily

There's really not an awful lot of difference between the two, they have wildly different storage mechanisms but they each have their fairly similar benefits. If you want to learn something that might be a requisite skill for a job, I would also look at alternatives such as time based and column based systems like InfluxDB and the unbelievably fast and flexible ClickHouse. While they may seem like an unlikely fit for a personal bug tracker app, there's no reason not to use them. Since I got into InfluxDB people have been requesting it a lot and I'll be using ClickHouse for all large databases, probably forever. Expand your horizons beyond your competition's.