- Create a new table with an identical structure as the source table and which has JSON column(s) defined with the binary storage format.
- Use INSERT...SELECT to populate the new table. This will implicitly cast the JSON text data to the binary format.
Note that the space needed to store data in one storage format is not guaranteed to be equal to the space needed to store the identical data in a different storage format. For example, data stored as text may be smaller than data stored as BSON, or vice versa. Therefore, it is necessary to determine how much space is required to store the data in the new format. You can use the StorageSize method to perform this analysis before creating the new table.
For example, suppose you have an existing table like the following:
CREATE TABLE jsonTextTable(id INTEGER, j JSON(1000) CHARACTER SET LATIN); /*load many rows of data*/
If you want to migrate the JSON data from being stored as text to being stored as BSON, you must determine the maximum size needed using a query like the following:
SELECT MAX(j.StorageSize('BSON')) FROM jsonTextTable;
The result of this query can then be used to create the new table. In this example, suppose the result was X.
CREATE TABLE jsonBSONTable(id INTEGER, j JSON(X) STORAGE FORMAT BSON);
INSERT INTO jsonBSONTable SELECT * FROM jsonTextTable;