Query nested arrays in ArangoDB

Question

I'm looking for a way to query nested arrays in ArangoDB.

The JSON structure I have is:

{
  "uid": "bykwwla4prqi",
  "category": "party",
  "notBefore": "2016-04-19T08:43:35.388+01:00",
  "notAfter": "9999-12-31T23:59:59.999+01:00",
  "version": 1.0,
  "aspects": [
    "participant"
  ],
  "description": [
    { "value": "User Homer Simpson, main actor in 'The Simpsons'", "lang": "en"}
  ],
  "properties": [
    {
      "property": [
        "urn:project:domain:attribute:surname"
      ],
      "values": [
        "Simpson"
      ]
    },
    {
      "property": [
        "urn:project:domain:attribute:givennames"
      ],
      "values": [
        "Homer",
        "Jay"
      ]
    }
  ]
}

I tried to use a query like the following to find all parties having a given name 'Jay':

FOR r IN resource
FILTER "urn:project:domain:attribute:givennames" IN r.properties[*].targets[*]
   AND "Jay" IN r.properties[*].values[*]
RETURN r

but unfortunately it does not work - it returns an empty array. If I use a '1' instead of '*' for the properties array it works. But the array of the properties has no fixed structure.

Does anybody have an idea how to solve this?

Thanks a lot!

dothebart · Accepted Answer · 2016-05-09 11:56:46Z

You can inspect what the filter does using a simple trick: you RETURN the actual filter condition:

db._query(`FOR r IN resource  RETURN r.properties[*].property[*]`).toArray()
[ 
  [ 
    [ 
      "urn:project:domain:attribute:surname" 
    ], 
    [ 
      "urn:project:domain:attribute:givennames" 
    ] 
  ] 
]

which makes it pretty clear whats going on. The IN operator can only work on one dimensional arrays. You could work around this by using FLATTEN() to remove the sub layers:

db._query(`FOR r IN resource  RETURN FLATTEN(r.properties[*].property[*])`).toArray()
[ 
  [ 
    "urn:project:domain:attribute:surname", 
    "urn:project:domain:attribute:givennames" 
  ] 
]

However, while your documents are valid json (I guess its converted from xml?) you should alter the structure as one would do it in json:

"properties" : {
  "urn:project:domain:attribute:surname":[
      "Simpson"
  ],
  "urn:project:domain:attribute:givennames": [
      "Homer",
      "Jay"
  ]
}

Since the FILTER combination you specify would also find any other Jay (not only those found in givennames) and the usage of FLATTEN() will prohibit using indices in your filter statement. You don't want to use queries that can't use indices on reasonably sized collections for performance reasons.

In Contrast you can use an array index on givennames with the above document layout:

db.resource.ensureIndex({type: "hash",
                        fields:
      ["properties.urn:project:domain:attribute:givennames[*]"]
    })

Now doublecheck the explain for the query:

db._explain("FOR r IN resource FILTER 'Jay' IN " + 
            "r.properties.`urn:project:domain:attribute:givennames` RETURN r")
...
  6   IndexNode            1     - FOR r IN resource   /* hash index scan */
...
Indexes used:
By   Type   Collection   Unique   Sparse   Selectivity   Fields           Ranges
  6   hash   resource     false    false       100.00 % \
       [ `properties.urn:project:domain:attribute:givennames[*]` ] \
       ("Jay" in r.`properties`.`urn:project:domain:attribute:givennames`)

that its using the index.

I was looking for AQL to return documents based on similar condition like 'Jay' in array This helped , although I dont have to FLATTEN my result set though

Jeremy Caney · Accepted Answer · 2024-11-10 17:29:57Z

0

I came across a similar problem. My data set was an array of objects called offers, inside which another array called metaData was present.

data Set Example :

offers : [{
Country : 1,
Status:1,
OfferCode :"TEST",
EndUtc : 1234455,
metaData :[{name : "isNewUSer",message :"yes"},
{name : "cohort",message :"bro please"}]
}]

I wanted to fetch all the offers whose metaData had isNewUser at least once. Here is the sample query for this requirement.

for o in offers 
    let a = o.metaData 
    let c = (for m in a filter m.name == 'IsNewUser' return a)
    filter LENGTH(c) > 0 and o.Country == "14" and o.Status ==1
    return o.OfferCode

This will return all the OfferCodes which has IsNewUser in its metaData.

edited Nov 10, 2024 at 17:29

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Nov 5, 2024 at 8:27

anjan obalesh

11 bronze badge

Collectives™ on Stack Overflow

Query nested arrays in ArangoDB

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related