1

I am trying YugaByte's Cassandra API (YCQL) and interested in using the JSONB data type extensions.

But I am having trouble both updating an attribute in an existing JSONB column as well as adding a new attribute to an existing JSONB column.

Is this supported in YugaByte? Here is what I tried:

Consider the following example whichhas have one row with a simple key and JSONB column.

cqlsh:k> CREATE TABLE T (key int PRIMARY KEY, value jsonb);
cqlsh:k> INSERT INTO T(key, value) VALUES(1, '{"author": "Charles", "title": "Hello World"}');
cqlsh:k> SELECT * FROM T;

 key | value
-----+--------------------------------------------
   1 | {"author":"Charles","title":"Hello World"}

(1 rows)

So far so good.

If I try to update an existing attribute inside the doc, I see the following error:

cqlsh:k> UPDATE T SET value->'author' = 'Bruce' WHERE key=1;
InvalidRequest: Error from server: code=2200 [Invalid query] message="SQL error: \
Invalid Arguments. Corruption: JSON text is corrupt: Invalid value.

If I try to add a new attribute into an existing JSONB attribute, I get the following error;

cqlsh:k> UPDATE T SET value->'price' = '10' WHERE key=1;
InvalidRequest: Error from server: code=2200 [Invalid query] message="SQL error: \
Execution Error. Could not find member:

Is this supported, and if so what is the correct syntax?

John Blum
  • 7,381
  • 1
  • 20
  • 30
Srinath
  • 51
  • 2

1 Answers1

1

When updating a string value you must enclose the new value in double quotes inside the single quotes. For example:

cqlsh:k> UPDATE T SET value->'author' = '"Bruce"' WHERE key=1;
cqlsh:k> SELECT * FROM T;

 key | value
-----+------------------------------------------
   1 | {"author":"Bruce","title":"Hello World"}

(1 rows)

Regarding the second question on ability to add new attributes:

For UPDATE, currently (as of 1.1) YugaByte DB allows updating specific attributes if that attribute/field already exists, but does not allow addition of new attributes into an existing JSONB column. If you need to the latter, you need to read the old value into the app, and write the new json in its entirety.

Alan
  • 136
  • 1