Inconsistent Behavior Between map and jsonb fields in YCQL

[EmiPhil]

Jira Link: DB-2403 Code:

-- inconsistent json/map behavior, full example

create keyspace test;

create table test.example
(
    pkField int,
    mapField map<text, int>,
    jsonField jsonb,
    primary key ( pkField )
) with transactions = { 'enabled': true };

insert into test.example (pkField) values (1);

select * from test.example where pkField = 1;
-- pkfield | mapfield | jsonfield
-----------+----------+-----------
--       1 |     null |      null

-- Works as expected.
update test.example set mapField['Key'] = 1 where pkField = 1;
select * from test.example where pkField = 1;
-- pkfield | mapfield   | jsonfield
-----------+------------+-----------
--       1 | {'Key': 1} |      null

-- Fails.
update test.example set jsonField->'Key' = '{}' where pkField = 1;
-- Execution Error. Invalid Json value: update test.example set jsonField->'Key' = '{}' where pkField = 1 ^^^^^^ (ql error -300)

-- The failure occurs because the json is null, but that behavior is markedly different from the behavior of the map field:
update test.example set jsonField = '{}' where pkField = 1;
update test.example set jsonField->'Key' = '{}' where pkField = 1;
select * from test.example where pkField = 1;
-- pkfield | mapfield   | jsonfield
-----------+------------+------------
--       1 | {'Key': 1} | {"Key":{}}

It's a bit non-intuitive that the json style should fail in this case, especially when using a map collection does work. I'm not sure if the ysql side has the same sharp edge or not.

This is a big pain point in using a jsonb field in ycql when you need to add a collection of documents and you aren't sure what the current state of the json field is. In the map scenario, it doesn't matter because you can just add keys directly. But this limitation on the jsonb side means you first need to check if the field is null or not, and if it is null add an empty json field, which leads to some race conditions where you could overwrite a json document by resetting it to {} while another process has already done that and added a new document. Sorry that this section is a bit rambley, I can get into why this behavior causes a lot of pain more if needed.

The ycql engine should know that the zero value of a jsonb field is {} and create that when doing something like update test.example set jsonField->'Key' = '{}' where pkField = 1; on a null json field.

[ddorian]

I'm not sure if the ysql side has the same sharp edge or not.

It fails on YSQL too, but you can do upserts there for these cases.

[ddorian]

cc @tedyu if you were working on this recently ?

[tedyu]

I am not familiar with CQL map construct.

[ddorian]

@tedyu map is fine, the issue is only about jsonb, upserting a key on a jsonb when it's null