I have database structure, close to this:
+--------------+---------+------------------------------+
| hostname | cont | numbers |
+--------------+---------+------------------------------+
| host01 |{"k":"v"}|LIST(`[["1", "123456", ...]]`)|
| host02 |{"k":"v"}|LIST(`[["1", "654321", ...]]`)|
+--------------+---------+------------------------------+
I want to get certain record, by filtering bin "Numbers" and "Hostname". It should contain a string with certain number, that I get from other component. In code above, it's '123456'
Basically, I used to search it in this way, until two-dimensional array is required:
stmt := aerospike.NewStatement(namespaceName, setName)
stmt.SetPredExp(
aerospike.NewPredExpStringBin("hostname"),
aerospike.NewPredExpStringValue(inputHostName),
aerospike.NewPredExpStringRegex(0),
aerospike.NewPredExpStringBin("deleted"),
aerospike.NewPredExpStringValue("true"),
aerospike.NewPredExpStringEqual(),
aerospike.NewPredExpNot(),
aerospike.NewPredExpAnd(2))
)
I've been looking though possible way to filter through array, but didn't get any solutions.
The way I found on the Internet was something like this:
stmt := aerospike.NewStatement("test", "settest")
qPolicy := aerospike.NewQueryPolicy()
qPolicy.PredExp = append(qPolicy.PredExp,
aerospike.NewPredExpStringVar("v"),
aerospike.NewPredExpStringValue(id),
aerospike.NewPredExpStringEqual(),
aerospike.NewPredExpListBin("numbers"),
aerospike.NewPredExpListIterateOr("v"))
recordSet, err := aerospikeClient.client.Query(qPolicy, stmt)
But it doesn't work.
Is it possible to search for Record, if I'll provide Predicated Expression using both hostname, and somehow parse array?
UPD:
I found out, this case can be solved by using Filter Expression, especially with usage of CDTContext. But Aerospike documentation has very poor information about usage of this tool, especially on Go code. I found only this method, which could help, but I don't understand how to use it correctly in my case:
func aerospike.ExpListGetByValue(returnType aerospike.ListReturnType, value *aerospike.Expression, bin *aerospike.Expression, ctx ...*aerospike.CDTContext) *aerospike.Expression
UPD2 By using ExpListGetByValue I've tried to make a query, but it couldn't find anything. It looks like this:
stmt := aerospike.NewStatement(namespaceName, setName)
qPolicy := aerospike.NewQueryPolicy()
qPolicy.FilterExpression = aerospike.ExpEq(
aerospike.ExpListGetByValue(
aerospike.ListReturnTypeCount,
aerospike.ExpStringVal(numbVal),
aerospike.ExpListBin("numbers")),
aerospike.ExpIntVal(1))
recordSet, err := aerospikeClient.client.Query(qPolicy, stmt)
This recordSet comes empty, though "numbers" bin do contain this numVal
UPD3 The problem was solved. I added context for nested array. Complete Filter expression will look like this
qPolicy.FilterExpression = aerospike.ExpEq(
aerospike.ExpListGetByValue(
aerospike.ListReturnTypeCount,
aerospike.ExpStringVal("123456"),
aerospike.ExpListBin("numbers"),
aerospike.CtxListIndex(-1)),
aerospike.ExpIntVal(1))
I was running your test with the new-and-revised filter you created in your second update and it seems to work. Here is the full code I used (based on the Aerospike Example directory from the Git repo):
/*
* Copyright 2014-2022 Aerospike, Inc.
*
* Portions may be licensed to Aerospike, Inc. under one or more contributor
* license agreements.
*
* Licensed under the Apache License, Version 2.0 (the "License"); you may not
* use this file except in compliance with the License. You may obtain a copy of
* the License at http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
* License for the specific language governing permissions and limitations under
* the License.
*/
package main
import (
"log"
as "github.com/aerospike/aerospike-client-go/v5"
shared "github.com/aerospike/aerospike-client-go/v5/examples/shared"
)
func main() {
testListExp(shared.Client)
log.Println("Example finished successfully.")
}
func testListExp(client *as.Client) {
log.Printf("Test List Expression")
key, _ := as.NewKey(*shared.Namespace, *shared.Set, "mapkey1")
client.Delete(shared.WritePolicy, key)
binHostname := as.NewBin("hostname", "host01")
amap := map[string]string{
"k": "v",
}
list := []string{
"1",
"123456",
"1111",
"222",
}
binCont := as.NewBin("cont", amap)
binNumbers := as.NewBin("numbers", list)
client.PutBins(shared.WritePolicy, key, binHostname, binCont, binNumbers)
key, _ = as.NewKey(*shared.Namespace, *shared.Set, "mapkey2")
binHostname = as.NewBin("hostname", "host02")
list = []string{
"1",
"654321",
"2222",
"3333",
}
binNumbers = as.NewBin("numbers", list)
client.PutBins(shared.WritePolicy, key, binHostname, binCont, binNumbers)
// numbVal := "654321"
getPolicy := as.NewQueryPolicy()
getPolicy.FilterExpression =
as.ExpEq(
as.ExpListGetByValue(
as.ListReturnTypeCount,
as.ExpStringVal("654321"),
as.ExpListBin("numbers")),
as.ExpIntVal(1))
stmt := as.NewStatement(*shared.Namespace, *shared.Set)
recordSet, err := client.Query(getPolicy, stmt)
shared.PanicOnError(err)
for res := range recordSet.Results() {
log.Println(res.Record.Bins)
}
}
and the output was:
$ go run listExpression.go -h 172.17.0.3
2022/06/08 12:00:19 hosts: 172.17.0.3
2022/06/08 12:00:19 port: 3000
2022/06/08 12:00:19 user:
2022/06/08 12:00:19 password: *
2022/06/08 12:00:19 namespace: test
2022/06/08 12:00:19 set: testset
2022/06/08 12:00:19 Test List Expression
2022/06/08 12:00:19 map[cont:map[k:v] hostname:host02 numbers:[1 654321 2222 3333]]
2022/06/08 12:00:19 Example finished successfully.