ArangoDB - how to find an item in a collection by a substring of an array item?

There is a collection with this structure:

{
   "user": 1, 
   "timestamp": 216354113151, 
   "message": "asddsaasddsaa", 
   "data": [
     "name = Jack & hash = s5w464t35w145df13s5df4sdg & id = 2" 
     "name = Mansonack & hash = xga5fd7h68745v46ed2 & id = 18" 
   ] 
}

      

I need to find an item of this collection, there is a value in the key data that contains a string containing the substring xga5fd7h68745v46ed2

Can this query be written? How will it look like?

+3


source to share


2 answers


You can iterate over the elements of the array as you would any other list:



FOR d IN collection_name
FILTER IS_LIST(d.data)
FOR data IN d.data
FILTER CONTAINS(data, "xga5fd7h68745v46ed2")
RETURN d

      

+2


source


If your dataset contains multiple instances of a substring that you are looking for and only want to return unique documents containing the search value, use the following query:

// what you are looking for in string
LET searchValue = 'xga5fd7h68745v46ed2'
// go through items of your collection
FOR item IN collection_name
    // store result of subquery in temporary variable
    LET wasItemFound = FIRST((
        // go through elements of your data array
        FOR str IN item.data
            // search for desired value within the element
            FILTER CONTAINS(str, searchValue)
            // we need to find only single occurance of searchValue
            LIMIT 1
            // return true if it was found
            RETURN true
    ))
    // give me only items which contained value you searched for
    FILTER wasItemFound == true
    RETURN item

      

The query suggested by pluma might do the job for your usecase if the values ​​you are looking for are unique across all elements of the dataset. If there are multiple cases of substrings you are looking for in the elements of the dataset, then the query returns duplicate documents. For example, consider this sample dataset:

[
  {
    "user": 1,
    "data": [
      "name = Jack & hash = abc & id = 2",
      "name = Mansonack & hash = abcd & id = 18"
    ]
  },
  {
    "user": 2,
    "data": [
      "name = Jack & hash = abb & id = 2",
      "name = Mansonack & hash = abc & id = 18",
      "name = xxx& hash = aaa & id = 18"
    ]
  }
]

      



If you use this query

FOR d IN collection_name
    FILTER IS_LIST(d.data)
    FOR data IN d.data
        FILTER CONTAINS(data, "ab")
        RETURN d

      

It will return 4 documents even if the collection contains only 2. However, the very first query in this post will only return 2. To be clear, I am not saying that pluma's solution is wrong, it just depends on what you expect.

+1


source







All Articles