Aerospike - Query on Map Keys

Question

I have a question about Aerospike DB.

I have set of students, and each student (record key is StudentId), has a map (bin) of <CourseId, Grade>. I'm trying to create some queries, and I'm not sure what is the correct way to do it.

I have variable containing a List of <String> courseIds.

The queries that I want to create are:

1. For each student, get all the courseIds that exists in the map and in the list.
2. For each student, get all the courseIds that exist only in their map, and not in the list.

What is the best approach here? Should I use UDF?

Thanks.

Answer 1

This is the kind of thing a record UDF is good for - extending functionality that doesn't yet exist in predicate filtering. The record UDF can take the bin name as the first argument, your list variable as its second argument, and an optional third argument for deciding whether this is an 'IN' or 'NOT IN', then iterate through it against the map of course IDs.

You can apply this record UDF to every record matched by a scan or query running against the set containing the students.

test.lua

function list_compare(rec, bin, l, not_in_l)
  if rec[bin] then
    local b = rec[bin]
    if (tostring(getmetatable(rec[bin])) == tostring(getmetatable(list()))) then
      iter = list.iterator
    elseif (tostring(getmetatable(rec[bin])) == tostring(getmetatable(map()))) then
      iter = map.values
    else
      return nil
    end
    local s = {}
    local l_keys = {}
    if (not_in_l ~= nil) then
      for v in list.iterator(l) do
        l_keys[v] = 1
      end
    end
    for i in list.iterator(l) do
      for v in iter(b) do
        if (not_in_l == nil) then
          if (i == v) then
            s[v] = 1
          end
        else
          if (i ~= v and not l_keys[v]) then
            s[v] = 1
          end
        end
      end
    end
    local keys = {}
    for k,v in pairs(s) do
      table.insert(keys, k)
    end
    table.sort(keys)
    return list(keys)
  end
end

In AQL:

$ aql
Aerospike Query Client
Version 3.15.1.2
C Client Version 4.3.0
Copyright 2012-2017 Aerospike. All rights reserved.
aql> register module './test.lua'
OK, 1 module added.
aql> insert into test.demo (PK,i,s,m,l) values ('88',6,'six',MAP('{"a":2, "b":4, "c":8, "d":16}'),LIST('[2, 4, 8, 16, 32, 128, 256]'))
OK, 1 record affected.

aql> select * from test.demo where PK='88'
+---+-------+--------------------------------------+-------------------------------------+
| i | s     | m                                    | l                                   |
+---+-------+--------------------------------------+-------------------------------------+
| 6 | "six" | MAP('{"a":2, "b":4, "c":8, "d":16}') | LIST('[2, 4, 8, 16, 32, 128, 256]') |
+---+-------+--------------------------------------+-------------------------------------+
1 row in set (0.001 secs)

aql> execute test.list_compare("l", LIST('[1,2,3,4]')) on test.demo where PK='88'
+----------------+
| list_compare   |
+----------------+
| LIST('[2, 4]') |
+----------------+
1 row in set (0.002 secs)

aql> execute test.list_compare("l", LIST('[1,2,3,4]'),1) on test.demo where PK='88'
+-------------------------------+
| list_compare                  |
+-------------------------------+
| LIST('[8, 16, 32, 128, 256]') |
+-------------------------------+
1 row in set (0.001 secs)

aql> execute test.list_compare("m", LIST('[1,2,3,4]')) on test.demo where PK='88'
+----------------+
| list_compare   |
+----------------+
| LIST('[2, 4]') |
+----------------+
1 row in set (0.001 secs)

aql> execute test.list_compare("m", LIST('[1,2,3,4]'), 1) on test.demo where PK='88'
+-----------------+
| list_compare    |
+-----------------+
| LIST('[8, 16]') |
+-----------------+
1 row in set (0.000 secs)