List fields

If a table specifies a list to hold data, then use INSERT or UPDATE to enter data.

Procedure

  • Insert data into the list, enclosing values in square brackets.

    INSERT INTO cycling.upcoming_calendar (
      year, month, events
    ) VALUES (
      2015, 06, [ 'Criterium du Dauphine', 'Tour de Suisse' ]
    );
  • Use the UPDATE statement to insert values into the list. Prepend an element to the list by enclosing it in square brackets and using the addition (+) operator.

    UPDATE cycling.upcoming_calendar 
    SET events = [ 'Tour de France' ] + events 
    WHERE year = 2015
      AND month = 06;
  • Append an element to the list by switching the order of the new element data and the list name in the UPDATE statement.

    UPDATE cycling.upcoming_calendar 
    SET events = events + [ 'Tour de France' ] 
    WHERE year = 2017
      AND month = 05;

    These update operations are implemented internally without any read-before-write. Appending and prepending a new element to the list writes only the new element.

  • Add an element at a particular position using the list index position in square brackets.

    UPDATE cycling.upcoming_calendar 
    SET events[2] = 'Vuelta Ciclista a Venezuela' 
    WHERE year = 2015
      AND month = 06;

    To add an element at a particular position, the database reads the entire list, and then rewrites the part of the list that needs to be shifted to the new index positions. Consequently, adding an element at a particular position results in greater latency than appending or prefixing an element to a list.

  • Remove an element from a list, use the DELETE statement and the list index position in square brackets. For example, remove the first event in the list.

    DELETE events[0]
    FROM cycling.upcoming_calendar
    WHERE year = 2015
      AND month = 07;

The method of removing elements using an indexed position from a list requires an internal read. In addition, the client-side application could only discover the indexed position by reading the whole list and finding the values to remove, adding additional latency to the operation. If another thread or client prepends elements to the list before the operation is done, incorrect data will be removed.

  • Remove all elements having a particular value using the UPDATE statement, the subtraction operator (-), and the list value in square brackets.

    UPDATE cycling.upcoming_calendar
    SET events = events - ['Tour de France Stage 10']
    WHERE year = 2015
      AND month = 07;

    Using the UPDATE statement as shown in this example is recommended over the last example because it is safer and faster.

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