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 the INSERT 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"
    }';