DynamoDB: Handle container types correctly

[amotl]

About

Coming from a fix to handle map/object types correctly, and corresponding attempts to introduce CrateDB SQL casts to get it right, we may have spotted an anomaly when processing JSON data on OBJECTs vs. ARRAYs.

Details

Do you think this is an anomaly that deserves a ticket on crate/crate? The first statement casts an object successfully from JSON, while the second one croaks on the list/array.

cr> select '{"foo": "bar"}'::OBJECT;
+----------------+
| {"foo"='bar'}  |
+----------------+
| {"foo": "bar"} |
+----------------+

select '["foobar"]'::ARRAY(TEXT);
SQLParseException[Cannot cast `'["foobar"]'` of type `text` to type `text_array`]

References

• DynamoDB: Handle maps correctly #32
• DynamoDB: Fix object (Map) representation to CrateDB #31

[amotl]

@seut responded:

We cannot cast a JSON->Array. Without quotes it should work because it is then parsed as an array literal instead of text.

@amotl said:

Got it.

cr> select ['foobar']::ARRAY(TEXT);
+------------+
| ['foobar'] |
+------------+
| ["foobar"] |
+------------+

[amotl]

First of all, thanks for your elaborations.

This makes it complicated if an ARRAY contains OBJECTs, because then we cannot “simply” serialize them to JSON, but have to use the special syntax, for which there is of course no ready-made recursive serializer a la json.dumps().

Is there any chance CrateDB could provide symmetric support on the SQL level to handle both container data types OBJECT and ARRAY equally well?

[seut]

I think one should cast to the JSON type when working with json data instead of using our dedicated container structures.
Casting the string literal to json already works with top-level arrays inside the json string:

cr> select '["foobar"]'::json;
+--------------+
| '["foobar"]' |
+--------------+
| ["foobar"]   |
+--------------+
SELECT 1 row in set (0.012 sec)

But this won't help much when trying to insert this into a array defined type or dynamic object.
Inserting into a array defined column will still fail due to the missing JSON->ARRAY cast feature:

cr> create table t1 (a array(text));
CREATE OK, 1 row affected (0.381 sec)
cr> insert into t1 (a) values('["foobar"]'::json);
SQLParseException[Cannot cast expressions from type `json` to type `text_array`]

Inserting it into a dynamic object will store the json array as text instead of an array:

CREATE OK, 1 row affected (0.357 sec)
cr> insert into t1 (o) values ({foo=1});
INSERT OK, 1 row affected (0.075 sec)
cr> update t1 set o['arr'] = '["foobar"]'::json;
UPDATE OK, 1 row affected (0.049 sec)
cr> select pg_typeof(o['arr']) from t1;
+--------------------------------+
| pg_catalog.pg_typeof(o['arr']) |
+--------------------------------+
| text                           |
+--------------------------------+
SELECT 1 row in set (0.007 sec)

So the only solution seems to support the JSON -> ARRAY cast.

[amotl]

So the only solution seems to support the JSON -> ARRAY cast.

Thank you so much for your evaluations. So, until CrateDB will gain that feature, we need to use the non-JSON syntax to relay ARRAY types in a generic way.

Primitive inner types

It works well for those.

cr> UPDATE "foo" SET data['tags'] = ['foo', 'bar'];
UPDATE OK, 1 row affected (0.049 sec)
cr> select pg_typeof(data['tags']) from foo;
+------------------------------------+
| pg_catalog.pg_typeof(data['tags']) |
+------------------------------------+
| text_array                         |
+------------------------------------+

Complex inner types

It also works well for those.

cr> UPDATE "foo" SET data['supertags'] = [{foo='bar', baz='qux'}];
UPDATE OK, 1 row affected (0.052 sec)
cr> select pg_typeof(data['supertags']) from foo;
+-----------------------------------------+
| pg_catalog.pg_typeof(data['supertags']) |
+-----------------------------------------+
| object_array                            |
+-----------------------------------------+

So, I figure it's a bummer, but not a blocker.

[amotl]

Fixed with GH-34, and released with v0.0.11.