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
– Mrityunjay Singh, Commented Oct 27, 2015 at 15:16
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
– stj, Commented Oct 27, 2015 at 15:36
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
– Mrityunjay Singh, Commented Oct 27, 2015 at 15:50
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
– Mrityunjay Singh, Commented Oct 27, 2015 at 15:52
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
– CodeManX, Commented Nov 6, 2015 at 15:52

stj · Accepted Answer · 2016-01-04 09:47:36Z

1

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.

edited Jan 4, 2016 at 9:47

answered Oct 27, 2015 at 15:51

stj

9,11722 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Mrityunjay Singh Over a year ago

Thanx stj...., Can you please guide me how i can remove the restriction regarding the more/less levels of nesting....

Mrityunjay Singh Over a year ago

Is there any way to remove this restriction??

Mrityunjay Singh Over a year ago

and can you suggest me some good tutorial for me as the beginner of ArangoDB??

stj Over a year ago

The structure is hard-coded in the query I wrote. There is a FOR loop for each level of nesting. I don't think there is a way to process arbitrarily-structured documents in AQL without knowing anything about the structure. This will also be difficult with other languages I think. What I therefore suggest is to define at least a minimal structure and use this for all documents.

stj Over a year ago

Re tutorials: the official documentation is at docs.arangodb.com and there's also a list of cookbook recipes here: docs.arangodb.com/cookbook

|

Collectives™ on Stack Overflow

Iterative querying the ArangoDB using AQL

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related