You cannot use the ALTER TABLE statement to migrate JSON data from the text format to one of the binary formats. To perform this conversion, do the following:
- Create a new table with the same structure as the source table with the JSON columns defined with the binary storage format.
- Use INSERT SELECT to populate the new table. This implicitly casts the JSON text data to the binary format.
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 the reverse. Before creating the new table, use the StorageSize method to determine how much space is required to store the data in the new format.
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;