Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • 1. Linear scalability
      • 2. Fault tolerance
      • 3. Global distribution
      • 4. Auto sharding
      • 5. Tunable reads
      • 6. Observability
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Explore sample applications
    • Deploy
      • Checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm Chart
        • Helm configuration
        • Local SSD
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure Cloud Providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
      • Authorization
        • 1. RBAC Model
        • 2. Create Roles
        • 3. Grant permissions
      • TLS encryption
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at Rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster config
      • Upgrade deployment
      • Diagnostics reporting
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit config flags
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting overview
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DOMAIN
          • ALTER TABLE
          • BEGIN
          • COMMENT
          • COMMIT
          • COPY
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE INDEX
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DROP DATABASE
          • DROP DOMAIN
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TYPE
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Keywords
        • Reserved Names
      • YCQL
        • Quick Start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • yb-master
      • yb-tserver
      • ysqlsh
      • cqlsh
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • TablePlus
  • RELEASES
    • Release history
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Layered architecture
      • Basic concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTOR GUIDES
    • Get involved
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> APIs > YCQL >

MAP, SET, LIST

Attention

This page documents an earlier version. Go to the latest (v2.3) version.
  • Synopsis
    • LIST
    • MAP
    • SET
  • Syntax
  • Semantics
  • Examples
    • CREATE TABLE with collections
    • INSERT collection data
    • UPDATE collection column
    • Collection expressions
    • UPDATE map and list elements
  • See also

Synopsis

Collection data types are used to specify columns for data objects that can contains more than one value.

LIST

LIST is an ordered collection of elements. All elements in a LIST must be of the same primitive type. Elements can be prepend or append by + operator to a list, removed by - operator, and referenced by their indexes of that list by [] operator.

MAP

MAP is an sorted collection of pairs of elements, a key and a value. The sorting order is based on the key values and is implementation-dependent. With their key values, elements in a MAP can be set by the [] operator, added by the + operator, and removed by the - operator. When queries, the element pairs of a map will be returned in the sorting order.

SET

SET is a sorted collection of elements. The sorting order is implementation-dependent. Elements can be added by + operator and removed by - operator. When queried, the elements of a set will be returned in the sorting order.

Syntax

type_specification ::= { LIST<type> | MAP<key_type:type> | SET<key_type> }

list_literal ::= '[' [ expression ...] ']'

map_literal ::= '{' [ { expression ':' expression } ...] '}'

set_literal ::= '{' [ expression ...] '}'

Where

  • Columns of type LIST, 'MAP', or SET cannot be part of the PRIMARY KEY.
  • type must be a non-parametric data type or a frozen data type.
  • key_type must be any data type that is allowed in a primary key (Currently FROZEN and all non-parametric data types except BOOL).
  • For map_literal the left-side expression represents the key and the right-side one represents the value.
  • expression is any well formed CQL expression. See Expression for more information on syntax rules.

Semantics

  • Type parameters must be simple types or frozen types (collections and user-defined types must be frozen to be used as collection parameters).
  • Columns of type LIST, MAP, and SET cannot be part of the PRIMARY KEY.
  • Implicitly, values of collection data types are neither convertible nor comparable to other data types.
  • Each expression in a collection literal must evaluate to a value convertible to the corresponding parameter data type.
  • Comparisons on collection values are not allowed (e.g. in WHERE or IF clauses).
  • Empty collections are treated as null values.

Note

Collections are designed for storing small sets of values that are not expected to grow to arbitrary size (such as phone numbers or addresses for a user rather than posts or messages). While collections of larger sizes are allowed, they may have a significant impact on performance for queries involving them. In particular, some list operations (insert at an index and remove elements) require a read-before-write.

Examples

CREATE TABLE with collections

  • Collection types are used like simple types (except they are not allowed in primary key).
cqlsh:example> CREATE TABLE users(username TEXT PRIMARY KEY, 
                                  emails SET<TEXT>,
                                  phones MAP<TEXT,TEXT>,
                                  top_cities LIST<TEXT>);

INSERT collection data

  • Collection values are inserted by setting all their elements at once.
cqlsh:example> INSERT INTO users(username, emails, phones, top_cities) 
               VALUES ('foo', 
                       {'[email protected]', '[email protected]'}, 
                       {'home' : '999-9999', 'mobile' : '000-0000'}, 
                       ['New York', 'Paris']);

Empty collections are the same as nulls.

cqlsh:example> INSERT INTO users(username, emails, phones, top_cities) VALUES ('bar', { }, { }, [ ]);
cqlsh:example> SELECT * FROM users;
 username | emails                             | phones                                     | top_cities
----------+------------------------------------+--------------------------------------------+-----------------------
      bar |                               null |                                       null |                  null
      foo | {'[email protected]', '[email protected]'} | {'home': '999-9999', 'mobile': '000-0000'} | ['New York', 'Paris']

UPDATE collection column

  • Collection values can be updated by setting all their elements at once.
cqlsh:example> UPDATE users SET emails = {'[email protected]'} WHERE username = 'bar';
cqlsh:example> UPDATE users SET phones = {'home' : '123-45678'} WHERE username = 'bar';
cqlsh:example> UPDATE users SET top_cities = ['London', 'Tokyo'] WHERE username = 'bar';
cqlsh:example> SELECT * FROM users;
 username | emails                             | phones                                     | top_cities
----------+------------------------------------+--------------------------------------------+-----------------------
      bar |                {'[email protected]'} |                      {'home': '123-45678'} |   ['London', 'Tokyo']
      foo | {'[email protected]', '[email protected]'} | {'home': '999-9999', 'mobile': '000-0000'} | ['New York', 'Paris']

Collection expressions

  • Collection elements can be added with + or removed with -.
cqlsh:example> UPDATE users SET emails = emails + {'[email protected]'} WHERE username = 'foo';
cqlsh:example> UPDATE users SET emails = emails - {'[email protected]', 'c.example.com'} WHERE username = 'foo';
cqlsh:example> UPDATE users SET phones = phones + {'office' : '333-3333'} WHERE username = 'foo';
cqlsh:example> SELECT * FROM users;
 username | emails                               | phones                                                           | top_cities
----------+--------------------------------------+------------------------------------------------------------------+-----------------------
      bar |                  {'[email protected]'} |                                            {'home': '123-45678'} |   ['London', 'Tokyo']
      foo | {'[email protected]', '[email protected]'} | {'home': '999-9999', 'mobile': '000-0000', 'office': '333-3333'} | ['New York', 'Paris']
  • To remove map elements only the relevant keys need to be given (as a set).
cqlsh:example> UPDATE users SET phones = phones - {'home'} WHERE username = 'foo';
cqlsh:example> SELECT * FROM users;
 username | emails                               | phones                                       | top_cities
----------+--------------------------------------+----------------------------------------------+-----------------------
      bar |                  {'[email protected]'} |                        {'home': '123-45678'} |   ['London', 'Tokyo']
      foo | {'[email protected]', '[email protected]'} | {'mobile': '000-0000', 'office': '333-3333'} | ['New York', 'Paris']
  • List elements can be either prepended or appended.
cqlsh:example> UPDATE users SET top_cities = top_cities + ['Delhi'] WHERE username = 'foo';
cqlsh:example> UPDATE users SET top_cities = ['Sunnyvale'] + top_cities WHERE username = 'foo';
cqlsh:example> UPDATE users SET top_cities = top_cities - ['Paris', 'New York'] WHERE username = 'foo';
cqlsh:example> SELECT * FROM users;
 username | emails              | phones                                       | top_cities
----------+---------------------+----------------------------------------------+------------------------
      bar | {'[email protected]'} |                        {'home': '123-45678'} |    ['London', 'Tokyo']
      foo | {'[email protected]'} | {'mobile': '000-0000', 'office': '333-3333'} | ['Sunnyvale', 'Delhi']

UPDATE map and list elements

  • Maps allow referencing elements by key.
cqlsh:example> UPDATE users SET phones['mobile'] = '111-1111' WHERE username = 'foo';
cqlsh:example> UPDATE users SET phones['mobile'] = '345-6789' WHERE username = 'bar' IF phones['mobile'] = null;
cqlsh:example> SELECT * FROM users;
 username | emails                               | phones                                       | top_cities
----------+--------------------------------------+----------------------------------------------+-----------------------
      bar |                  {'[email protected]'} |  {'home': '123-45678', 'mobile': '345-6789'} |   ['London', 'Tokyo']
      foo | {'[email protected]', '[email protected]'} | {'mobile': '111-1111', 'office': '333-3333'} | ['New York', 'Paris']
  • Lists allow referencing elements by index (numbering starts from 0).
cqlsh:example> UPDATE users SET top_cities[0] = 'San Francisco' WHERE username = 'bar';
cqlsh:example> UPDATE users SET top_cities[1] = 'Mumbai' WHERE username = 'bar' IF top_cities[1] = 'Tokyo';
cqlsh:example> SELECT * FROM users;
 username | emails                               | phones                                       | top_cities
----------+--------------------------------------+----------------------------------------------+-----------------------------
      bar |                  {'[email protected]'} |  {'home': '123-45678', 'mobile': '345-6789'} | ['San Francisco', 'Mumbai']
      foo | {'[email protected]', '[email protected]'} | {'mobile': '111-1111', 'office': '333-3333'} |       ['New York', 'Paris']

See also

Data Types

  • Synopsis
    • LIST
    • MAP
    • SET
  • Syntax
  • Semantics
  • Examples
    • CREATE TABLE with collections
    • INSERT collection data
    • UPDATE collection column
    • Collection expressions
    • UPDATE map and list elements
  • See also
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.