Querying non-required properties of objects

CrateDB SQL
1

I have a schema where I’m storing some data in an OBJECT column. Initially I was trying to avoid defining the schema of the object a priori, just defining it as OBJECT(DYNAMIC). I ran into a problem with this when querying for properties that might not exist on any records. Here’s an example:

CREATE TABLE test_table (id INT PRIMARY KEY, obj OBJECT(DYNAMIC))
INSERT INTO test_table (id, obj) VALUES (1, { "a"=1}),(2, {"a"=NULL})
UPDATE test_table SET obj['a']=2 WHERE obj['a'] IS NULL

That all works. But if I try the following…
UPDATE test_table SET obj[‘b’]=2 WHERE obj[‘b’] IS NULL

I get an error:
SQLActionException[ColumnUnknownException: Column obj[‘b’] unknown]

Do I have to define the schema of the object in order to make this work, or is there a way to structure the statement so that unknown/missing properties in objects won’t cause it to fail?

2

Referencing an unknown sub-column is currently not supported at CrateDB.

When using an object type with a DYNAMIC policy, sub-columns are available only after at least one entry exists. This first entry will also define the data type of that sub-column which all follow up writes must also use.

But your use-case is valid, I think we could come up with something like the ? json operator at PostgreSQL.
Please open a feature request at GH related to that, or even a contribution? :wink:

3

Thanks @smu. I put in a feature request: https://github.com/crate/crate/issues/9383.