Inserting JSON formatted values
Inserting JSON data with the INSERT command for testing queries.
In a production database, inserting columns and column values programmatically is more practical than using cqlsh. The CQL INSERT commands supports JSON to provide a manual testing from the cqlsh command line utility.
Use the following
syntax:
INSERT INTO [keyspace_name.]table_name JSON '{"column_name": value [,…]}' [DEFAULT UNSET];
Note: Enclose
all values other than numbers in double quotes. Booleans, UUID, and other data
types typically recognized in cqlsh must be in double quotes.
Procedure
-
To insert JSON data, add
JSON
to theINSERT
command.INSERT INTO cycling.cyclist_category JSON '{ "category" : "GC", "points" : 780, "id" : "829aa84a-4bba-411f-a4fb-38167a987cda", "lastname" : "SUTHERLAND" }';
-
When upserting data if any columns are missing from the JSON, the value in the
missing column is overwritten with null (by default). The following removes the
lastname
value "SUTHERLAND" from the previous example:INSERT INTO cycling.cyclist_category JSON '{ "category" : "Sprint", "points" : 780, "id" : "829aa84a-4bba-411f-a4fb-38167a987cda" }';
-
Use the DEFAULT UNSET option to only overwrite values found in the JSON
string:
INSERT INTO cycling.cyclist_category JSON '{ "category" : "Sprint", "points" : 780, "id" : "829aa84a-4bba-411f-a4fb-38167a987cda" }' DEFAULT UNSET;
-
Only the PRIMARY KEY fields are required when inserting a new row, any other
column not define in the JSON is set to null:
INSERT INTO cycling.cyclist_category JSON '{ "category" : "Sprint", "points" : 700, "id" : "829aa84a-4bba-411f-a4fb-38167a987cda" }';