# Querying non-required properties of objects

**URL:** <https://community.cratedb.com/t/querying-non-required-properties-of-objects/309>\
**Category:** SQL\
**Created:** [November 13, 2019, 4:50pm UTC](https://community.cratedb.com/t/querying-non-required-properties-of-objects/309 "2019-11-13T16:50:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dieseldjango](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/dieseldjango/32/138_2.png) [@dieseldjango](https://community.cratedb.com/u/dieseldjango)\
**Post date:** [November 13, 2019, 4:50pm UTC](https://community.cratedb.com/t/querying-non-required-properties-of-objects/309/1 "2019-11-13T16:50:22Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![smu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/smu/32/60_2.png) [@smu](https://community.cratedb.com/u/smu)\
**Post date:** [November 26, 2019, 8:23am UTC](https://community.cratedb.com/t/querying-non-required-properties-of-objects/309/2 "2019-11-26T08:23:59Z")

</div>

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? 😉

---

<div class="post-metadata">

**Author:** ![dieseldjango](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/dieseldjango/32/138_2.png) [@dieseldjango](https://community.cratedb.com/u/dieseldjango)\
**Post date:** [November 26, 2019, 5:46pm UTC](https://community.cratedb.com/t/querying-non-required-properties-of-objects/309/3 "2019-11-26T17:46:27Z")

</div>

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