Inserting JSON formatted values

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.

Prerequisites

Use the following syntax:

INSERT INTO [<keyspace_name>.]<table_name> JSON
'{
  "column_name": value [,…]
}'
( DEFAULT ( NULL | UNSET) );

By default (or if DEFAULT NULL is explicitly used), a column omitted from the JSON map will be set to NULL, meaning that any pre-existing value for that column will be removed (resulting in a tombstone being created). Alternatively, if the DEFAULT UNSET directive is used after the value, omitted column values will be left unset, meaning that pre-existing values for those column will be preserved.

Enclose all values other than numbers in double quotes. Booleans, UUID, and other data types typically recognized in cqlsh must be in double quotes.

To insert JSON data, add JSON to the INSERT statement.

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"
}'
DEFAULT UNSET;

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

Query and output of all rows in the table:

SELECT * FROM cycling.cyclist_category;

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

 category      | points | id                                   | lastname
---------------+--------+--------------------------------------+------------
 One-day-races |    367 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
 One-day-races |    198 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
    Time-trial |    182 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
    Time-trial |      3 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |    780 | 829aa84a-4bba-411f-a4fb-38167a987cda |       null
        Sprint |    700 | 829aa84a-4bba-411f-a4fb-38167a987cda |       null
        Sprint |     39 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |      0 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
            GC |   1324 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
            GC |   1269 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
            GC |    780 | 829aa84a-4bba-411f-a4fb-38167a987cda | SUTHERLAND

(11 rows)

Inserting JSON formatted values

Prerequisites

Was this helpful?

Give Feedback