Iterative querying the ArangoDB using AQL

Question

I have stored the JSON data in ArangoDB collection in the following format.

{ 
  "data": {
    "1":   [ {"db_name": "DSP"}, {"rel": "2"} ], 
    "2":   [ {"rel_name": "DataSource"}, {"tuple": "201"}, {"tuple": "202"}, {"tuple": "203"} ],
    "201": [ {"src_id": "Pos201510070"}, {"src_name": "Postgres"}, {"password": "root"}, {"host": "localhost"}, {"created_date": "20151007"}, {"user_name": "postgres"}, {"src_type": "Structured"}, {"db_name": "postgres"}, {"port": "None"} ],
    "202": [ {"src_id": "pos201510060"}, {"src_name": "Postgres"},{"password": "root"}, {"host": "localhost"}, {"created_date": "20151006"}, {"user_name": "postgres"}, {"src_type": "Structured"}, {"db_name": "DSP"}, {"port": "5432"} ],
    "203": [ {"src_id": "pos201510060"}, {"src_name": "Postgres"}, {"password": "root"}, {"host": "localhost"}, {"created_date": "20151006"}, {"user_name": "postgres"},{"src_type": "Structured"},{"db_name": "maindb"},{"port": "5432"} ]
  }
}

I am new to ArangoDB. I have no idea about the storing and querying the data from ArangoDB. In my data there is no any predefined key, and the data get populated with time. My data is just like a semi-structured data which do not have any fixed number of attributes, and little bit complex due to its iterative list structure.

First, anyone can suggest me the best way for storing the above format in ArangoDB.

Second, I want to query this data in following manner: by specifying any key (not known in advance, by specifying it at runtime), or by specify combinations of key and value pair, e.g., Key1 == value1, or the combination using AND or OR logical operators like Key1 == value1 AND Key2 == value2 OR Key3== value3.

So, how we can iterate over the above data?

I have tried the query "For u in Collecton_name return u" It is just like the querying a relational data using select * from table_name; — Mrityunjay Singh
It's a bit unclear what kind of query result is actually desired. For example, to iterate over all attributes in each document, you can use nested FOR loops in AQL as follows: FOR doc IN collection LET attributes = ATTRIBUTES(doc.data) FOR attribute IN attributes RETURN { key: attribute, value: doc.data[attribute] } — stj
I want to access only those where db_name == "main_db" or i want the list the values corresponding to the attribute name like src_type etc.. — Mrityunjay Singh
Thanks, I also want to know that whether we can formulate the query like above on run tike as the number of conditions gets increased. — Mrityunjay Singh
Can you explain why your data structure uses an array of objects, with only a single attribute each? Looks like your data model is not well defined. To understand your problem better, it might help if you post the equivalent SQL query you would use to solve the issue. — CodeManX

stj stj · Accepted Answer · 2015-10-27T15:51:29

If you really want to store data in a structure like this, without any predefined attribute names, you can still iterate over the data by converting it into a normalized structure on-the-fly.

The following AQL query creates a flat list of key/value pairs for each document:

FOR doc IN collection 
  LET attributes = ATTRIBUTES(doc.data) 
  FOR attribute IN attributes 
    FOR arrayItem IN doc.data[attribute] 
      LET key = ATTRIBUTES(arrayItem)[0] 
      LET value = arrayItem[key] 
      RETURN { _key: doc._key key: key, value: value }

The result of this query will be something like this:

[ 
  { 
    "_key" : "864582648369", 
    "key" : "password", 
    "value" : "root" 
  }, 
  { 
    "_key" : "864582648369", 
    "key" : "db_name", 
    "value" : "postgres" 
  }, 
  { 
    "_key" : "864582648369", 
    "key" : "port", 
    "value" : "None" 
  }, 
  ...
]

Now you can apply the filtering easily by adding the filter conditions of choice:

FOR doc IN collection 
  LET attributes = ATTRIBUTES(doc.data) 
  FOR attribute IN attributes 
    FOR arrayItem IN doc.data[attribute] 
      LET key = ATTRIBUTES(arrayItem)[0] 
      LET value = arrayItem[key] 
      FILTER key == 'password' || key == 'port' || (key == 'db_name' && value == 'postgres') 
      RETURN { _key: doc._key, key: key, value: value }

Note that when the data structure changes (more/less levels of nesting), the above won't work anymore. The query assumes documents with a structure as presented in the question.

Iterative querying the ArangoDB using AQL

1 Answers