How to return the results of a bulk insert?

Question

How do I return the array of newly inserted documents? I want to perform a bulk insert from Java.

INSERT {
  text: @text0,
  createdAt: @createdAt0
} IN messages

LET result0 = { id: NEW._key, text: NEW.text, createdAt: NEW.createdAt }

INSERT {
  text: @text1,
  createdAt: @createdAt1
} IN messages

LET result1 = { id: NEW._key, text: NEW.text, createdAt: NEW.createdAt }

RETURN [result0, result1]

Is there a better way to collect the results from each insert, other than defining a new variable to keep the results in it?

How many documents are you looking at inserting via a single command? Have you looked at using a Foxx MicroService in ArangoDB to help you do it? For true bulk inserts, it's unusual to return the newly inserted documents, as they are usually optimised for speed and loading data quickly. But if you are adding smaller bursts of documents of variable lengths, I would look at setting up a Foxx endpoint to do it for you. What's nice about that is you can implement more database schema protection and add additional business logic. — David Thomas, Mar 13 '20 at 07:35
@DavidThomas It shouldn't be more than 10 messages. I'd like to keep it simple by using AQL only, this way I can easily test the query in the Arango Management Interface. — vovahost, Mar 13 '20 at 11:12
I answered a question a while ago, and on [this question](https://stackoverflow.com/questions/56034784/how-to-represent-non-unique-parent-child-relationship-as-graph/56040457#56040457) I have an example of how to bulk save documents into a collection via arangosh, the command line tool. Would that work for you? Remember that the _key attribute of a document is randomly generated on INSERT but it increments, so you could do queries in the UI where _key was greater than a given number to see the newly inserted documents. — David Thomas, Mar 13 '20 at 11:46
Using that technique with arangosh, you can even save the commands in a js file and run them, a super easy way to bulk load initial documents, create, drop databases, modify indexes, whatever you need. — David Thomas, Mar 13 '20 at 11:48
@DavidThomas I'm executing AQL queries from java. The java sdk has a `Collection.insertDocuments` function which takes a list of documents (it's probably using FOXX underneath as you mentioned). The only issue is that it returns _id, _key, _rev fields as well. There is no option to rewrite the fields. I'd want to add the `id` field which will contain _key value. I guess I can just process the list of returned documents returned as result and remove the fields I don't need and add the `id` field to it. — vovahost, Mar 13 '20 at 11:55
Also in the queries I'm using binding parameters `@name` to sanitize the data entered by the user. I'm not sure the `Collection.insertDocuments` is doing the same. There's no info on that. — vovahost, Mar 13 '20 at 11:58
My use-case: The user types some messages while offline, when online again the cached messages a being sent to backend, where I need to perform a bulk insert. — vovahost, Mar 13 '20 at 11:58

score 1 · Accepted Answer · answered Mar 13 '20 at 13:48

This query should do what you want.

FOR d IN [
    {text:@text0, createdAt: @createdAt0}, {text:@text1, createdAt: @createdAt1}
]
INSERT d INTO abc
RETURN {
    id: NEW._id,
    text: d.text,
    createdAt: d.createdAt
}

An example response from the AQL is:

[
  {
    "id": "abc/21971344",
    "text": "apple",
    "createdAt": 1584107091
  },
  {
    "id": "abc/21971345",
    "text": "banana",
    "createdAt": 1584108473
  }
]

So long as the end user doesn't craft the AQL it should be fine, especially since you're using parameters.

If you ever used the product beyond testing, I'd definitely recommend looking at Foxx, as it gives you a layer of abstraction, hosted (relatively) within the database, which at the least stops consumers from having to execute AQL queries at all, rather they just communicate via REST endpoints.

PS. This query format also works:

FOR d IN @messages
INSERT d INTO abc
RETURN {
    id: NEW._id,
    text: d.text,
    createdAt: d.createdAt
}

Where @messages is

"messages": [
    {
        "text": "apple",
        "createdAt": 1584107091
    },
    {
        "text": "banana",
        "createdAt": 1584108473
    }
]

In the second example you showed, are the values in `messages` array sanitized? — vovahost, Mar 14 '20 at 14:56
Yes, the parameter is an array of objects, so it's not possible to do SQL injection with them as the AQL command is consuming the parameter purely as an array. — David Thomas, Mar 15 '20 at 23:26

How to return the results of a bulk insert?

1 Answers1