JSON Documents Beta

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

The JSON document data type makes it very easy to model use-cases which require a flexible schema. YugabyteDB supports a JSONB data type that parse, store and query JSON documents natively.

In this tutorial, we are going to look the example of an online bookstore. The bookstore has a number of books, each of these books has a number of attributes describing it like the name, the author, and so on. We are going to model these book details as a JSON object using the JSONB datatype.

If you haven’t installed YugabyteDB yet, do so first by following the Quick Start guide.

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./bin/yb-ctl create

2. Create a table with a JSON column

Connect to the cluster using cqlsh.

  1. $ ./bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.
  2. [cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
  3. Use HELP for help.
  4. cqlsh>

Create a keyspace for the online bookstore.

  1. cqlsh> CREATE KEYSPACE store;

Create a table with the book id as the primary key and a jsonb column to store the book details.

  1. cqlsh> CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );

You can verify the schema of the table by describing it.

  1. cqlsh> DESCRIBE TABLE store.books;
  1. CREATE TABLE store.books (
  2.     id int PRIMARY KEY,
  3.     details jsonb
  4. ) WITH default_time_to_live = 0;

3. Insert sample data

Let us seed the books table with some sample data. Note the following about the sample data:

  • all book details have the book’s name, its author and the publication year
  • some books have a genre attribute specified, while others do not

Paste the following into the cqlsh shell.

  1. INSERT INTO store.books (id, details) VALUES
  2.   (1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
  3. INSERT INTO store.books (id, details) VALUES 
  4.   (2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
  5. INSERT INTO store.books (id, details) VALUES 
  6.   (3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
  7. INSERT INTO store.books (id, details) VALUES 
  8.   (4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
  9. INSERT INTO store.books (id, details) VALUES 
  10.   (5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');

4. Execute queries against the documents

Let us execute some queries against the books table.To view .

Query all the data

We can view all the data inserted by running the following query.

  1. cqlsh> SELECT * FROM store.books;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   1 |                            {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  5.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  6.   2 |                                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  7.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Query a particular book by id

If we want the details of a book whose id is known (in this example query, we are querying the details of the book with id 5).

  1. cqlsh> SELECT * FROM store.books WHERE id=5;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}

Query based on json attributes

We currently support two operators for json. The -> operator returns a result which is a jsondocument and further json operations can be applied to the result. The ->> operator returns astring result and as a result no further json operations can be performed after this.

If we want to query all the books whose author is William Shakespeare

  1. cqlsh> SELECT * FROM store.books where details->'author'->>'first_name' = 'William' AND details->'author'->>'last_name' = 'Shakespeare';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |     {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}

If we want to retrieve the author for all entries:

  1. cqlsh> SELECT id, details->>'author' as author from store.books;
  1.  id | author
  2. ----+----------------------------------------------------
  3.   5 |     {"first_name":"Stephen","last_name":"Hawking"}
  4.   1 | {"first_name":"William","last_name":"Shakespeare"}
  5.   4 |     {"first_name":"Charles","last_name":"Dickens"}
  6.   2 | {"first_name":"William","last_name":"Shakespeare"}
  7.   3 |     {"first_name":"Charles","last_name":"Dickens"}

Query based on array elements

You can do this as shown below.

  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>0 = 'Mark';
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>2 = 'Jeff';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}

Working with integers within json documents

The operators -> and ->> introduced in the previous sections return a jsonb and string typerespectively, but sometimes its useful to consider json attributes as numeric types so that we canapply the appropriate logical/arithmetic operators.

For this purpose, we can use the CAST function to handle integers within the json document:

  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1700;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  5.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) = 1950;
  1.  id | details
  2. ----+--------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1600 AND CAST(details->>'year' AS integer) <= 1900;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   1 |                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |                    {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  5.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Updating json documents

  • Update entire JSONB document

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details = '{"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}' WHERE id = 1;
  1. cqlsh> SELECT * FROM store.books WHERE id = 1;
  1.  id | details
  2. ----+-----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}
  • Update a JSONB object value.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author'->>'first_name' = '"Steve"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB array element.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'editors'->>1 = '"Jack"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB subdocument.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author' = '{"first_name":"John", "last_name":"Doe"}' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"John","last_name":"Doe"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./bin/yb-ctl create

2. Create a table with a JSON column

Connect to the cluster using cqlsh.

  1. $ ./bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.
  2. [cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
  3. Use HELP for help.
  4. cqlsh>

Create a keyspace for the online bookstore.

  1. cqlsh> CREATE KEYSPACE store;

Create a table with the book id as the primary key and a jsonb column to store the book details.

  1. cqlsh> CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );

You can verify the schema of the table by describing it.

  1. cqlsh> DESCRIBE TABLE store.books;
  1. CREATE TABLE store.books (
  2.     id int PRIMARY KEY,
  3.     details jsonb
  4. ) WITH default_time_to_live = 0;

3. Insert sample data

Let us seed the books table with some sample data. Note the following about the sample data:

  • all book details have the book’s name, its author and the publication year
  • some books have a genre attribute specified, while others do not

Paste the following into the cqlsh shell.

  1. INSERT INTO store.books (id, details) VALUES
  2.   (1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
  3. INSERT INTO store.books (id, details) VALUES 
  4.   (2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
  5. INSERT INTO store.books (id, details) VALUES 
  6.   (3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
  7. INSERT INTO store.books (id, details) VALUES 
  8.   (4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
  9. INSERT INTO store.books (id, details) VALUES 
  10.   (5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');

4. Execute queries against the documents

Let us execute some queries against the books table.To view .

Query all the data

We can view all the data inserted by running the following query.

  1. cqlsh> SELECT * FROM store.books;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   1 |                            {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  5.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  6.   2 |                                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  7.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Query a particular book by id

If we want the details of a book whose id is known (in this example query, we are querying the details of the book with id 5).

  1. cqlsh> SELECT * FROM store.books WHERE id=5;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}

Query based on json attributes

We currently support two operators for json. The -> operator returns a result which is a jsondocument and further json operations can be applied to the result. The ->> operator returns astring result and as a result no further json operations can be performed after this.

If we want to query all the books whose author is William Shakespeare

  1. cqlsh> SELECT * FROM store.books where details->'author'->>'first_name' = 'William' AND details->'author'->>'last_name' = 'Shakespeare';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |     {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}

If we want to retrieve the author for all entries:

  1. cqlsh> SELECT id, details->>'author' as author from store.books;
  1.  id | author
  2. ----+----------------------------------------------------
  3.   5 |     {"first_name":"Stephen","last_name":"Hawking"}
  4.   1 | {"first_name":"William","last_name":"Shakespeare"}
  5.   4 |     {"first_name":"Charles","last_name":"Dickens"}
  6.   2 | {"first_name":"William","last_name":"Shakespeare"}
  7.   3 |     {"first_name":"Charles","last_name":"Dickens"}

Query based on array elements

You can do this as shown below.

  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>0 = 'Mark';
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>2 = 'Jeff';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}

Working with integers within json documents

The operators -> and ->> introduced in the previous sections return a jsonb and string typerespectively, but sometimes its useful to consider json attributes as numeric types so that we canapply the appropriate logical/arithmetic operators.

For this purpose, we can use the CAST function to handle integers within the json document:

  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1700;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  5.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) = 1950;
  1.  id | details
  2. ----+--------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1600 AND CAST(details->>'year' AS integer) <= 1900;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   1 |                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |                    {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  5.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Updating json documents

  • Update entire JSONB document

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details = '{"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}' WHERE id = 1;
  1. cqlsh> SELECT * FROM store.books WHERE id = 1;
  1.  id | details
  2. ----+-----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}
  • Update a JSONB object value.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author'->>'first_name' = '"Steve"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB array element.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'editors'->>1 = '"Jack"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB subdocument.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author' = '{"first_name":"John", "last_name":"Doe"}' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"John","last_name":"Doe"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./yb-docker-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./yb-docker-ctl create

2. Create a table with a JSON column

Connect to cqlsh on node 1.

  1. $ docker exec -it yb-tserver-n1 /home/yugabyte/bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.
  2. [cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
  3. Use HELP for help.
  4. cqlsh>

Create a keyspace for the online bookstore.

  1. cqlsh> CREATE KEYSPACE store;

Create a table with the book id as the primary key and a jsonb column to store the book details.

  1. cqlsh> CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );

You can verify the schema of the table by describing it.

  1. cqlsh> DESCRIBE TABLE store.books;
  1. CREATE TABLE store.books (
  2.     id int PRIMARY KEY,
  3.     details jsonb
  4. ) WITH default_time_to_live = 0;

3. Insert sample data

Let us seed the books table with some sample data. Note the following about the sample data:

  • all book details have the book’s name, its author and the publication year
  • some books have a genre attribute specified, while others do not

Paste the following into the cqlsh shell.

  1. INSERT INTO store.books (id, details) VALUES
  2.   (1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
  3. INSERT INTO store.books (id, details) VALUES 
  4.   (2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
  5. INSERT INTO store.books (id, details) VALUES 
  6.   (3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
  7. INSERT INTO store.books (id, details) VALUES 
  8.   (4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
  9. INSERT INTO store.books (id, details) VALUES 
  10.   (5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');

4. Execute queries against the documents

Let us execute some queries against the books table.To view .

Query all the data

We can view all the data inserted by running the following query.

  1. cqlsh> SELECT * FROM store.books;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   1 |                            {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  5.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  6.   2 |                                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  7.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Query a particular book by id

If we want the details of a book whose id is known (in this example query, we are querying the details of the book with id 5).

  1. cqlsh> SELECT * FROM store.books WHERE id=5;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}

Query based on json attributes

We currently support two operators for json. The -> operator returns a result which is a jsondocument and further json operations can be applied to the result. The ->> operator returns astring result and as a result no further json operations can be performed after this.

If we want to query all the books whose author is William Shakespeare

  1. cqlsh> SELECT * FROM store.books where details->'author'->>'first_name' = 'William' AND details->'author'->>'last_name' = 'Shakespeare';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |     {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}

If we want to retrieve the author for all entries:

  1. cqlsh> SELECT id, details->>'author' as author from store.books;
  1.  id | author
  2. ----+----------------------------------------------------
  3.   5 |     {"first_name":"Stephen","last_name":"Hawking"}
  4.   1 | {"first_name":"William","last_name":"Shakespeare"}
  5.   4 |     {"first_name":"Charles","last_name":"Dickens"}
  6.   2 | {"first_name":"William","last_name":"Shakespeare"}
  7.   3 |     {"first_name":"Charles","last_name":"Dickens"}

Query based on array elements

You can do this as shown below.

  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>0 = 'Mark';
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>2 = 'Jeff';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}

Working with integers within json documents

The operators -> and ->> introduced in the previous sections return a jsonb and string typerespectively, but sometimes its useful to consider json attributes as numeric types so that we canapply the appropriate logical/arithmetic operators.

For this purpose, we can use the CAST function to handle integers within the json document:

  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1700;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  5.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) = 1950;
  1.  id | details
  2. ----+--------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1600 AND CAST(details->>'year' AS integer) <= 1900;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   1 |                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |                    {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  5.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Updating json documents

  • Update entire JSONB document

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details = '{"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}' WHERE id = 1;
  1. cqlsh> SELECT * FROM store.books WHERE id = 1;
  1.  id | details
  2. ----+-----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}
  • Update a JSONB object value.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author'->>'first_name' = '"Steve"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB array element.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'editors'->>1 = '"Jack"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB subdocument.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author' = '{"first_name":"John", "last_name":"Doe"}' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"John","last_name":"Doe"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./yb-docker-ctl destroy

1. Setup - create universe and table

If you have a previously running local universe, destroy it using the following.

  1. $ kubectl delete -f yugabyte-statefulset.yaml

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ kubectl apply -f yugabyte-statefulset.yaml

Check the Kubernetes dashboard to see the 3 yb-tserver and 3 yb-master pods representing the 3 nodes of the cluster.

  1. $ minikube dashboard

Kubernetes Dashboard

2. Create a table with a JSON column

Connect to cqlsh on node 1.

  1. $ kubectl exec -it yb-tserver-0 /home/yugabyte/bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.
  2. [cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
  3. Use HELP for help.
  4. cqlsh>

Create a keyspace for the online bookstore.

  1. cqlsh> CREATE KEYSPACE store;

Create a table with the book id as the primary key and a jsonb column to store the book details.

  1. cqlsh> CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );

You can verify the schema of the table by describing it.

  1. cqlsh> DESCRIBE TABLE store.books;
  1. CREATE TABLE store.books (
  2.     id int PRIMARY KEY,
  3.     details jsonb
  4. ) WITH default_time_to_live = 0;

3. Insert sample data

Let us seed the books table with some sample data. Note the following about the sample data:

  • all book details have the book’s name, its author and the publication year
  • some books have a genre attribute specified, while others do not

Paste the following into the cqlsh shell.

  1. INSERT INTO store.books (id, details) VALUES
  2.   (1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
  3. INSERT INTO store.books (id, details) VALUES 
  4.   (2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
  5. INSERT INTO store.books (id, details) VALUES 
  6.   (3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
  7. INSERT INTO store.books (id, details) VALUES 
  8.   (4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
  9. INSERT INTO store.books (id, details) VALUES 
  10.   (5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');

4. Execute queries against the documents

Let us execute some queries against the books table.To view .

Query all the data

We can view all the data inserted by running the following query.

  1. cqlsh> SELECT * FROM store.books;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   1 |                            {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  5.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  6.   2 |                                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  7.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Query a particular book by id

If we want the details of a book whose id is known (in this example query, we are querying the details of the book with id 5).

  1. cqlsh> SELECT * FROM store.books WHERE id=5;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}

Query based on json attributes

We currently support two operators for json. The -> operator returns a result which is a jsondocument and further json operations can be applied to the result. The ->> operator returns astring result and as a result no further json operations can be performed after this.

If we want to query all the books whose author is William Shakespeare

  1. cqlsh> SELECT * FROM store.books where details->'author'->>'first_name' = 'William' AND details->'author'->>'last_name' = 'Shakespeare';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |     {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}

If we want to retrieve the author for all entries:

  1. cqlsh> SELECT id, details->>'author' as author from store.books;
  1.  id | author
  2. ----+----------------------------------------------------
  3.   5 |     {"first_name":"Stephen","last_name":"Hawking"}
  4.   1 | {"first_name":"William","last_name":"Shakespeare"}
  5.   4 |     {"first_name":"Charles","last_name":"Dickens"}
  6.   2 | {"first_name":"William","last_name":"Shakespeare"}
  7.   3 |     {"first_name":"Charles","last_name":"Dickens"}

Query based on array elements

You can do this as shown below.

  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>0 = 'Mark';
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
  1. cqlsh> SELECT * FROM store.books WHERE details->'editors'->>2 = 'Jeff';
  1.  id | details
  2. ----+----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}

Working with integers within json documents

The operators -> and ->> introduced in the previous sections return a jsonb and string typerespectively, but sometimes its useful to consider json attributes as numeric types so that we canapply the appropriate logical/arithmetic operators.

For this purpose, we can use the CAST function to handle integers within the json document:

  1. cqlsh> SELECT  FROM store.books WHERE CAST(details->>'year' AS integer) > 1700;

_

  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
  4.   4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  5.   3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

_

  1. cqlsh> SELECT  FROM store.books WHERE CAST(details->>'year' AS integer) = 1950;
  1.  id | details
  2. ----+--------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  1. cqlsh> SELECT * FROM store.books WHERE CAST(details->>'year' AS integer) > 1600 AND CAST(details->>'year' AS integer) <= 1900;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   1 |                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
  4.   2 |                    {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
  5.   3 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}

Updating json documents

  • Update entire JSONB document

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details = '{"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}' WHERE id = 1;
  1. cqlsh> SELECT * FROM store.books WHERE id = 1;
  1.  id | details
  2. ----+-----------------------------------------------------------------------------------------------------------------------------------
  3.   1 | {"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}
  • Update a JSONB object value.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author'->>'first_name' = '"Steve"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB array element.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'editors'->>1 = '"Jack"' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+------------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"Steve","last_name":"Dickens"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
  • Update a JSONB subdocument.

You can do this as shown below.

  1. cqlsh> UPDATE store.books SET details->'author' = '{"first_name":"John", "last_name":"Doe"}' WHERE id = 4;
  1. cqlsh> SELECT * FROM store.books WHERE id = 4;
  1.  id | details
  2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
  3.   4 | {"author":{"first_name":"John","last_name":"Doe"},"editors":["Robert","Jack","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ kubectl delete -f yugabyte-statefulset.yaml