1

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 ?

echarlus
  • 13
  • 3

1 Answers1

0

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.

Sebastian Utz
  • 719
  • 3
  • 9
  • Hi Sebastian, thanks for the answer, this solved my problem. I was misled into using a string by the fact that for objects I had also used strings to transmit them and crate was processing them fine... Now I'm transferring both arrays and objets as native json and it works. – echarlus Oct 22 '17 at 10:39