I’m trying to insert a row using Crate's 2.1.8 HTTP endpoint but it fails systematically with error 4003: My table contains a column which is an array of objects and it fails with error 4003 : “SQLActionException[ColumnValidationException: Validation failed for arr: ‘[{\“t\“:1}, {\“z\“:\“foo\“}]’ cannot be cast to type object_array]”
here’s the table creation : CREATE TABLE IF NOT EXISTS “doc”.“test” ( “arr” ARRAY(OBJECT (DYNAMIC)), “name” STRING )
now here’s my json :
{“stmt”:“INSERT INTO \“test\” (\“name\“,\“arr\“) VALUES (?,?)“, “args”:[“test”, “[{\“t\“:1}, {\“z\“:\“foo\“}]“]}
and my command to post the request : wget --header “Content-Type: application/json” --post-file query_test.json -O - ’http://localhost:4200/_sql?types&error_trace=true'
The result is: 4003 : “SQLActionException[ColumnValidationException: Validation failed for arr: ‘[{\“t\“:1}, {\“z\“:\“foo\“}]’ cannot be cast to type object_array]”
If I run this from the web console : INSERT INTO “test” (“name”,“arr”) VALUES (‘test’, [{“t”=1}, {“z”=‘foo’}]); It works fine ... any idea of what I’m doing wrong ?
Just remove the surrounding quotes of your array argument value and don't escape the quotes inside your array like:
{"stmt":"INSERT INTO \"test\" (\"name\",\"arr\") VALUES (?,?)", "args":["test", [{"t":1}, {"z":"foo"}]]}
otherwise it's a JSON string value not an array and so will CrateDB interpret this as a string.