JSONB

    Currently, updates to some attributes of a JSONB column require a full read-modify-write operation.Note that there are plans to enhance the JSONB data type to support efficient incremental updates ina future version.

    Syntax

    • Columns of type JSONB cannot be part of the PRIMARY KEY.
    • Implicitly, values of type JSONB are not convertible to other data types. JSONB types can becompared to TEXT/VARCHAR data type as long it represents valid json.
    • Values of text data types with correct format are convertible to JSONB.
    • JSONB value format supports text literals which are valid json.

    Operators and functions

    In some cases, we would like to process JSON attributes as numerics. For this purpose, we can usethe CAST function to convert text retrieved from the ->> operator to the appropriate numerictype.

    • Create table with a JSONB column
    1. cqlsh> CREATE KEYSPACE store;
    1. cqlsh> CREATE TABLE store.books ( id int PRIMARY KEY, details jsonb );
    • Insert JSONB documents
    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 
    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"] }');
    • Select from JSONB column
    1. cqlsh> SELECT * FROM store.books;
    1. id | details
    2. 5 | {"author":{"first_name":"Stephen","last_name":"Hawking"},"editors":["Melisa","Mark","John"],"genre":"science","name":"A Brief History of Time","year":1988}
    3. 1 |                            {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["John","Elizabeth","Jeff"],"name":"Macbeth","year":1623}
    4. 4 |      {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
    5. 2 |                                {"author":{"first_name":"William","last_name":"Shakespeare"},"editors":["Lysa","Mark","Robert"],"name":"Hamlet","year":1603}
    6. 3 |             {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Mark","Tony","Britney"],"genre":"novel","name":"Oliver Twist","year":1838}
    • Select with condition on JSONB object value
    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}
    • Select with condition on JSONB array element
    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}
    • Select with condition using on JSONB element
    1. id | details
    2. ----+--------------------------------------------------------------------------------------------------------------------------------------------------------
    3. 4 | {"author":{"first_name":"Charles","last_name":"Dickens"},"editors":["Robert","John","Melisa"],"genre":"novel","name":"Great Expectations","year":1950}
    • Update entire JSONB document
    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
    3.   1 | {"author":{"first_name":"Carl","last_name":"Sagan"},"editors":["Ann","Rob","Neil"],"genre":"science","name":"Cosmos","year":1980}
    • Update a JSONB object value.
      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.
      1. cqlsh> UPDATE store.books SET details->'editors'->>1 = '"Jack"' WHERE id = 4;
      1. cqlsh> SELECT * FROM store.books WHERE id = 4;
      • Update a JSONB subdocument.
      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}
      • Upsert: Update a missing JSONB document resulting in an insert.
      1. INSERT INTO store.books (id, details) VALUES
      2. (6, '{}');
      3. cqlsh> UPDATE store.books SET details->'editors' = '["Adam", "Bryan", "Charles"]' WHERE id = 6;
      1. cqlsh> SELECT * FROM store.books WHERE id = 6;
      1. id | details
      2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
      3. 6 | {"editors":["Adam","Bryan","Charles"]}
      • Upsert: Update a missing JSONB document resulting in an insert of a subdocument.
      1. cqlsh> UPDATE store.books SET details->'author' = '{"first_name":"Jack", "last_name":"Kerouac"}' WHERE id = 6;
      1. cqlsh> SELECT * FROM store.books WHERE id = 6;
      1. id | details
      2. ----+-------------------------------------------------------------------------------------------------------------------------------------------------
      3. 6 | {"author":{"first_name":"Jack","last_name":"Kerouac"},"editors":["Adam","Bryan","Charles"]}

      Note that JSONB upsert only works for JSON objects and not for other data types like arrays, integers, strings, etc. Additionally, only the leaf property of an object will be inserted if it is missing. We do not support upsert on non-leaf properties.

      See also