Yugabyte-db: JSONB: Support for root primitives

[tcf909]

Per a discussion with @kmuthukk

Opening up an issue for a discussion on allowing root primitives in column_type JSONB.

Technically string, numbers, and booleans are all valid JSONB types.

Should Yuga support for instance:

INSERT INTO k.t (id, json) VALUES (1, 'true');

We should track down what specifically is supported today, and what should be supported moving forward.

[kmuthukk]

@m-iancu - could you please confirm? Thanks a lot.

[m-iancu]

@tcf909 We do support JSONB primitive types: boolean, numeric (integer and non-integer), string and null.

Examples below (using cqlsh):
_Note: Omitting the cqlsh> shell marker so this can be copy pasted. Also this uses YugaByte's fork of cqlsh (should be packaged with YugaByte in bin/cqlsh, also found here) which has the JSONB support enhancement._

create keyspace foo;
use foo;
create table foo(h int primary key, jb jsonb);
-- boolean
insert into foo(h, jb) values (1, 'true');
-- numeric (integer)
insert into foo(h, jb) values (2, '-12');
-- numeric (non-integer)
insert into foo(h, jb) values (3, '12.5');
-- null
insert into foo(h, jb) values (4, 'null');
-- string (note the double quote).
insert into foo(h, jb) values (5, '"foo"');
-- random text (unquoted string). Should fail.
insert into foo(h, jb) values (6, 'abc');

InvalidRequest: Error from server: code=2200 [Invalid query] message="Query error: Invalid Arguments. Corruption: JSON text is corrupt: Invalid value.
insert into foo(h, jb) values (4, 'abc');
       ^^^^
 (error -304)"



md5-5473c9bde9a390bbbceb5ff33dc69e1f



select * from foo;



md5-5473c9bde9a390bbbceb5ff33dc69e1f



 h | jb
---+-------
 5 | "foo"
 1 |  true
 4 |  null
 2 |   -12
 3 |  12.5

(5 rows)



md5-3e3014d1ebf1f4de844cefe89358d264



select jb->'a' from foo;



md5-5473c9bde9a390bbbceb5ff33dc69e1f




 expr
------
 null
 null
 null
 null
 null

(5 rows)

[tcf909]

@m-iancu
From a standpoint of the client, will the servers handle a zero length buffer/string?

So if someone from the client went:

client.execute(`INSERT INTO foo(h, jb) VALUES (?, ?)`, [99, undefined], {prepared: true})

How should we translate that? Throw?

[tcf909]

Related https://github.com/YugaByte/cassandra-nodejs-driver/pull/1

[m-iancu]

@tcf909 I think the server will thrown an error for an empty string (i.e. '') but will allow empty value (i.e. null). But would have to double-check.

[tcf909]

Right now the node.js will not send a null value to the encoder class to encode We'd have to decide to change that.

Also the server sends null as a result when selecting a missing field. I guess these are similar, but null is a valid value to be stored and return (valid result).

Related #845

My thought would be if there is a missing value, it should not be considered null, since null could be a real value.

[m-iancu]

@tcf909 It's important to distinguish between the JSON null (i.e. the JSON primitive value 'null') and the YCQL null (i.e. the missing value returned as null). In my answer above I was not clear, but meant to refer to YCQL null, which is equivalent to e.g. not inserting any value in the JSON column at all.

In any case, I think it's probably fine to not allow it for now to avoid this exact confusion.