We have some migrations coming up in which we’ll move some of the MySQL schemas to Singlestore. The tables in those schemas have JSON fields as well. From what I have found on the web till now, it looks like JSON in MySQL and Singlestore are quite similar. Both enforce utf8mb4 character set and utf8mb4_bin collation by default. Also, unlike MariaDB, both do not allow non JSON data and on multiple other fronts as well it looks similar. The only thing is seems to be the way to fetch the JSON data is different in MySQL and Singlestore.
Do you guys suggest anything else that we’ll need to take care of related to JSON when coming from MySQL to Singlestore? We’re in very early stages so looking to gather and work on as many issues as possible early.
We do have a similar implementation, but it would be helpful to have additional specifics about how you use JSON in MySQL to better answer your question. I assume (at minimum) you are defining a data type of JSON in your tables and doing some manipulation to extract or generally query the data.
It would be helpful to know:
-What types of built-in programmability functions you are using in MySQL against JSON data?
-How are you loading JSON data?
-What other types of queries or operators do you use to access/manipulate JSON?
In general, it should be similar, but having all the details will allow us to provide some more clear recommendations of things to consider.
One thing to point out is that SingleStore doesn’t support utf8mb4 yet (that is coming in the next release). Our JSON will use MySQLs utf8 collation (which doesn’t support 4-byte chars). This shouldn’t impact you unless your storing emoji or some Chinese characters which need utf8mb4.
Understood about utf8mb4 and it should not be an issue to us.
Our Json usage is pretty basic. Our api’s load data and we access it using normal MySQL syntax for fetching Json data. I saw on singlestore we have an equivalent to that so we look good there.
Apart from that we use a couple of generic functions and JSON_EXTRACT is most used one there. I did not look much but surely there would be an equivalent there in singlestore as well. Correct?
So all in all it looks like with this type of basic functionality that we use, we should be able to export our data to singlestore and by changing the way we fetch the data in apis, we should be good unless you have anything else to point out?