Insert JSON formatted values

The CQL INSERT commands supports JSON. This is useful for testing and debugging inserts of JSON data to your tables.

For general information about INSERT and UPDATE commands, see Insert and update data.

Syntax

Use the following syntax to insert JSON data with the INSERT command. Enclose all values other than numbers in double quotes. Booleans, UUID, and other CQL data types must be in double quotes.

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

For example:

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "GC", 
  "points" : 780, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda",
  "lastname" : "SUTHERLAND"
}';

For a new insert, only the primary key fields are required. Any other column not defined in the JSON is set to null.

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint", 
  "points" : 700, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}';

Null versus unset

By default (or if DEFAULT NULL is explicitly used), a column omitted from the JSON map is set to NULL. This means that any pre-existing value for that column is removed, resulting in a tombstone.

Alternatively, if you use DEFAULT UNSET after the value, omitted column values are left unset, which preserves any pre-existing values in that column.

The following example removes the lastname value "SUTHERLAND" from the previous example because the default behavior is to mark unspecified values as null:

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 the values given in the JSON string:

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint",
  "points" : 780,
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda" }'
DEFAULT UNSET;

Use a SELECT statement to check the contents of the row and see if any values are null or unset:

SELECT * FROM cycling.cyclist_category;

Results

 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)

Insert JSON formatted values

Syntax

Null versus unset

Was this helpful?

Give Feedback