0

I would like to export the result of this query into a CSV file. Provided how to export this query from Arango shell?

Below is the query.

let lstofnodes=(for doc in Account  
    filter doc.fraud == true
        for v in 1..3 outbound doc graph "G1"
            return distinct v._id)
for d in Transaction
  filter d._from IN lstofnodes AND d._to IN lstofnodes 
  return distinct {"from":d._from, "to":d._to, "Amount": d.Amount, "TIme": d.Time}
Chaitanya Srinevas
  • 17
  • 4

1 Answers1

0

The accepted answer in the post I mentioned in my comment (Converting Json to CSV in Javascript) is completely usable in arangosh, you do program javascript in the shell after all.

Here is a console transcript of an example session using the technique:

Prepare db and aql modules:

127.0.0.1:8529@_system> var db = require("@arangodb").db

127.0.0.1:8529@_system> var aql = require("@arangodb").aql;

seperator could be \t, but we use a comma

127.0.0.1:8529@_system> const seperator = ',';

headers contains the names of the fields/properties of the documents we want to write to the csv file. They are used as column headers as well

127.0.0.1:8529@_system> let headers = ['prop1', 'prop2'];

The simple example collection I created for this example only contains two documents, so I don't filter anything - the .toArray() call is important here, the map call is otherwise not possible. This can bite you, if the amount of data you need to extract does not fit into the available memory.

127.0.0.1:8529@_system> var data = db._query(
    aql`FOR doc IN test RETURN doc`
 ).toArray();

This creates an array of strings. The first is the column header line, then follows the map call that extracts the properties from the documents and creates a csv string for each. If a field value can contain a comma, you would use enclosing quotes here, you would have to escape quote characters in values then

127.0.0.1:8529@_system> const csv = [
    headers.join(seperator),
    ...data.map(
           row => headers.map( 
               field => `${row[field]}`
           ).join(seperator)
    )
];

Finally we join the lines into one string seperated by newlines

127.0.0.1:8529@_system> csv.join("\n");
prop1,prop2
val,other
foo,baz

To write the javascript to a file:

127.0.0.1:8529@_system> var fs = require("fs");

127.0.0.1:8529@_system> fs.write("mydata.csv", csv.join("\n"));
true
Tom Regner
  • 6,856
  • 4
  • 32
  • 47
  • arangoexport \--type csv \--fields title,category1,category2 \--custom-query "FOR a in Account return a }" – Chaitanya Srinevas Oct 19 '22 at 13:28
  • This was the query that was given in the documentation and it does not seem to be working as it returns an syntax error in the arangoexport itself. – Chaitanya Srinevas Oct 19 '22 at 13:29
  • What I posted is an actual transcript, so that works and would produce csv for you, if your query would work. The small error snippet above looks like a syntax error somewhere, yes, but it is not conclusive - this would be a new question with as much information as possible. – Tom Regner Oct 19 '22 at 16:27
  • I executed the script that you had shared and was getting the following error which states permission denied cannot write file. – Chaitanya Srinevas Oct 20 '22 at 06:47
  • `fs.write` in this case writes to the working directory of the arangosh process, you have to have write permission there. – Tom Regner Oct 20 '22 at 07:26