1

I created a table with following syntax:

create table poll(poll_id string primary key,
poll_type_id integer,

poll_rating array(object as (rating_id integer,fk_user_id string, israted_image1 integer, israted_image2 integer, updatedDate timestamp, createdDate timestamp )),

poll_question string,
poll_image1 string, 
poll_image2 string
)

And I inserted a record without "poll_rating" field which is actually an array of objects fields.

Now when I try to update a poll_rating with the following commands:

update poll set poll_rating = [{"rating_id":1,"fk_user_id":-1,"israted_image1":1,"israted_image2":0,"createddate":1400067339.0496}] where poll_id = "f748771d7c2e4616b1865f37b7913707";

I'm getting an error message like this:

"SQLParseException[line 1:31: no viable alternative at input '[']; nested: ParsingException[line 1:31: no viable alternative at input '[']; nested: NoViableAltException;"

Can anyone tell me why I get this error when I try to update the array of objects fields.

admdrew
  • 3,790
  • 4
  • 27
  • 39
Ramesh_D
  • 689
  • 1
  • 7
  • 25

1 Answers1

4

Defining arrays and objects directly in SQL statement is currently not supported by our SQL parser, please use parameter substitution using placeholders instead as described here: https://crate.io/docs/current/sql/rest.html

Example using curl is as below:

curl -sSXPOST '127.0.0.1:4200/_sql?pretty' -d@- <<- EOF  
{"stmt": "update poll set poll_rating = ? where poll_id = ?",
 "args": [ [{"rating_id":1,"fk_user_id":-1,"israted_image1":1,"israted_image2":0,"createddate":1400067339.0496}], "f748771d7c2e4616b1865f37b7913707" ]
}  
EOF
user353gre3
  • 2,747
  • 4
  • 24
  • 27
Sebastian Utz
  • 719
  • 3
  • 9
  • i tried this. but No luck.. let me try once again !! – Ramesh_D May 15 '14 at 04:46
  • Thanks it worked. But i have another doubt, how to add poll_rating record on the existing poll_rating record. i.e like appending on a existing array object with new object. – Ramesh_D May 15 '14 at 05:20
  • @rameshd33 appending operations are not possible. You'll have to do the append operation client side and then update the whole array. – mfussenegger May 15 '14 at 07:21
  • Ok. But there can be concurrency issue if more than one person try to add, update or delete on single record array of objects. Am i right ? – Ramesh_D May 15 '14 at 08:16
  • Yes. Use optimistic concurrency control to avoid that: https://crate.io/docs/stable/sql/occ.html – Sebastian Utz May 22 '14 at 15:08
  • Thanks @SebastianUtz. Crate has a potential to challenge any DB in current market ! – Ramesh_D May 23 '14 at 08:02
  • @rameshd33 Just wanted to add that we have merged support for object literals into master - so with the next release (0.39) that feature will be available too: https://github.com/crate/crate/blob/master/docs/sql/ddl.txt#L299 – mfussenegger May 23 '14 at 18:56