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 UNSET];

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;
 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)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com