The JSONB data type with YCQL is a great feature but in its current state is somewhat limited when dealing with arrays. The documentation states: “Currently, updates to some attributes of a JSONB column require a full read-modify-write operation. Note that there are plans to enhance the JSONB data type to support efficient incremental updates in a future version.” Is there someone here who can speak to when these updates might be available?
The main features we need for arrays are append, insert, and delete.
By insert/delete, you mean at a certain index ? Like delete array[1] ?
Yes, by index
Also, how big do you think the arrays will end up ?
For our use, generally not more than 100 small strings
We’ve looked at using the map and list collection types, but they don’t quite have the flexibility we can get with JSONB.
Alternatively, we could use YSQL, which has JSONB support far beyond what we need, but it appears that the YCQL API is much faster and the CQL drivers all have built-in support for a distributed database.
It is generally hard to avoid read-modify-writes when using arrays, especially for positional operations. So, some operation (between the insert/delete/read) would end up getting penalized. But this is generally a bigger issue only when the array is very large - say millions of items.
If there are only 100s of items, the perf overhead may not be bad even if you simply read the whole array, make the change and replace it. Would you be able to perform these operations at the app layer and see if the performance meets your needs?
The collection types are less flexible because you have to declare the data type up front and can only insert that type of data. Typically it will be type ‘text’. That means you can’t insert sub documents in a list or map element. With JSONB, any object or list element can contain items of any data type and can contain other collections of any type.