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?
source to share
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.
source to share