jsonhttppostcrate

Using crate's HTTP API to insert an array of objects : error 4003


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 ?


Solution

  • 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.