Mapvalues query using golang

I have created a secondary index on a bin.

create mapvalues index item_state_idx on test.demo (shopping_cart) string

When I execute below query on console, I am getting result :

select * from test.demo in mapvalues where shopping_cart = "FL"

But, I am not able to find any way to implement this query using go. I have tried using : stmt.SetFilter(as.NewEqualFilter("shopping_cart ", “FL”)) But, it does not work. I get nil data. Any help would be highly appreciated.

Hi @sid1, welcome to the community!

From the way the secondary index was created, it appears that this is a map in the bin that you are looking to index, is that correct? If so, you are most likely missing the ctx in you stmt.SetFilter().

It would look something like:

After looking over this again, I think I’ve misunderstood what you’re looking at. If this is a bin with a map and you are looking for records that contain "FL" in the map then this is what you would need:

stmt.SetFilter(as.NewContainsFilter("shopping_cart", as.ICT_MAPVALUES, "FL")

Could you provide a look at the record structure that you are using? That will help to provide a more complete answer. Thanks!

@aanderson check: go - Aerospike query execution in golang - Stack Overflow and go - Reverse mapping in aerospike - Stack Overflow

@aanderson It works well for single json level, but is it possible to achieve same for nested json levels ? For example, I have : binName → A

json data in bin → "B" : { "X" : 2 }

can we do, select * from test.demo in mapvalues where A.B = 2

I have tried:

stmt.SetFilter(as.NewContainsFilter("A.B", as.ICT_MAPVALUES, 2))

but it didn’t work.

I have already created a secondary index on A.B using : client.CreateComplexIndex(nil, namespace, set, "nested_index", "A.B", as.NUMERIC, as.ICT_MAPVALUES)

@sid1 you would need to pass along a ctx to define the lookup map. That would look like this:

stmt.SetFilter(as.NewContainsFilter("A", as.ICT_MAPVALUES, 2, as.CtxMapKey("B"))
1 Like

Hi @aanderson , tried for 3 levels (A.B.C) but it didn’t work. stmt.SetFilter(as.NewContainsFilter("A", as.ICT_MAPVALUES, 10, as.CtxMapKey(as.NewStringValue("B")), as.CtxMapKey(as.NewStringValue("C"))))

Hi @sid1 to look at the values in “C” you would need to setup your index with this:

client.CreateComplexIndex(nil, namespace, set, "nested_index", "A", as.NUMERIC, as.ICT_MAPVALUES)

and then your filter would look like

stmt.SetFilter(as.NewContainsFilter("A", as.ICT_MAPVALUES, 2, as.CtxMapKey(as.NewValue("B")), as.CtxMapKey(as.NewValue("C"))))

Hi @aanderson , Not sure if I’m missing something but I get nil data. Here is what I did (for mapkeys, trying to access → A.TEST.NEW)

// Create statement

stmt := as.NewStatement(namespace, set, "A")

// Create complex index

_, err = client.CreateComplexIndex(nil, namespace, set, "new_index", "A", as.STRING, as.ICT_MAPKEYS, as.CtxMapKey(as.NewStringValue("TEST")))

// Set filter

stmt.SetFilter(as.NewContainsFilter("A", as.ICT_MAPKEYS, "NEW", as.CtxMapKey(as.NewStringValue("TEST"))))

Data looks like this : binName → A

data →

{
    "B": {
        "E": [
            909,
        ],
        "G": 0
    },
    "TEST": {
        "NEW": [
            468,
            198
        ]
    },
    "D": {
        "id": [
            123456
        ]
    }
}

Hey @sid1 your question keeps changing so I would highly recommend you checkout our client docs and tutorials to cover more of these scenarios.

Here is some code that you can run in our sandbox to see it working. Just select Go as the language and click connect. Run these snippets in order.

  1. Write the record
// Import Aerospike client library
import (
    "fmt"
    as "github.com/aerospike/aerospike-client-go/v6" 
)

// Establish connection to the server
client, err := as.NewClient("127.0.0.1", 3000)

// Initialize a write policy
policy := as.NewWritePolicy(0,0)
policy.SendKey = true

// Create a key in namespace "test" and set "data"
key, err := as.NewKey("test", "data", 1)

// Create the bin
bin := as.NewBin("A", map[string]interface{}{
  "B": map[string]interface{}{
      "E": []int{909},
      "G": 0},
  "TEST": map[string]interface{}{
      "NEW": []int{468, 198}},
  "D": map[string]interface{}{
      "id": []int{468, 198}}})

// Write the record to Aerospike
err = client.PutBins(policy, key, bin)
if err != nil {
    fmt.Printf("Error: %s", err)
}

record, err := client.Get(nil, key)
fmt.Printf("Create succeeded\nKey: %s\nRecord: %s\n", key.Value(), record)
  1. Create the index
task, err := client.CreateComplexIndex(nil, // write policy
    "test", // namespace
    "data", // set name
    "test_idx", // index name
    "A", // bin name
    as.STRING,
    as.ICT_MAPKEYS,
    as.CtxMapKey(as.NewStringValue("TEST")))

// Wait for the task to complete
<-task.OnComplete()
  1. Execute the query
// Create statement
stmt := as.NewStatement("test", "data")

// Create index filter
stmt.SetFilter(as.NewContainsFilter("A", as.ICT_MAPKEYS, as.NewStringValue("NEW"), as.CtxMapKey(as.NewStringValue("TEST"))))

// Execute the query
recordSet, err := client.Query(nil, stmt);

// Get the results
for records := range recordSet.Results() {
    if records != nil { 
        // Do something
        fmt.Printf("Record: %v \n", records.Record)
    }
}

recordSet.Close()

Hi @aanderson, sorry for the confusion. I realized that I was using aerospike version 5.7 and hence, getting nil data even after having right piece of code. I have upgraded aerospike version to 6.2 and now, everything is working as expected.

Thank you so much for your assistance.

1 Like