0

So I run CrateDB 3.3.3 and I have a table which has a column of type array of objects

CREATE TABLE IF NOT EXISTS "doc"."testarray" (
"id" INTEGER,
"myarraycol" ARRAY(OBJECT (DYNAMIC) AS (
  "avg" DOUBLE,
  "eventconditiondefid" INTEGER,
  "max" DOUBLE,
  "min" DOUBLE
))
)

I already know how to insert one row in it

insert into testarray (id, myarraycol) values (2, [{"min"=2,"max"=3,"avg"=0.5,"eventconditiondefid"=123},{"min"=0,"max"=1,"avg"=0.5,"eventconditiondefid"=456}]);

However, in my application, I bulk insert data into CrateDB via the HTTP endpoint.

https://crate.io/docs/crate/reference/en/latest/interfaces/http.html

I have it working for regular tables, but cannot make it work for a table with a column of array of objects. Can anyone tell me how I can make the bulk insert work with those types of columns? I can't seem to find any examples or documentations on it.

{ "stmt":"INSERT INTO testarray (  id, myarraycol) VALUES (  ?,   ?) ","bulk_args":[[1,[{"min"=0.616523,"max" = 1.10974,"Avg" = 0.874692,"EventConditionDefId" = 505}]]]}

(The above bulk insert code fails, it returns a (400) bad request)

Danielle Paquette-Harvey
  • 1,691
  • 1
  • 16
  • 31

1 Answers1

2

The problem is JSON it should be as follows:

{ "stmt":"INSERT INTO testarray (id, myarraycol) VALUES (?,?) ","bulk_args":[[1,[{"min":0.616523,"max" : 1.10974,"Avg" : 0.874692,"EventConditionDefId" : 505}]]]}
metase
  • 1,169
  • 2
  • 16
  • 29
  • Do you know if you can use the "ANY" operator on array of objects? https://crate.io/docs/crate/reference/en/latest/general/dql/selects.html#any-array – Danielle Paquette-Harvey Jul 02 '19 at 19:44
  • SELECT * FROM testarray WHERE ANY(myarraycol['EventConditionDefId'])=505 This query doesn't work, it returns "SQLActionException[SQLParseException: line 5:5: no viable alternative at input " – Danielle Paquette-Harvey Jul 02 '19 at 19:45
  • 1
    you need to invert the syntax there as follows: SELECT * FROM testarray WHERE 505 = ANY(myarraycol['EventConditionDefId']); – metase Jul 03 '19 at 11:28