Filter expression formation for a nested Map field (C# Aerospike.Client 5.3.2)

Client Libraries C# Client
, , ,
1

Hi there,

Help is needed in creating filter expression on a set with below bins:

bCustId (int)
bMonth (Secondary index)
bCategory (int)
bOrdDtls (Map<string, Map<string, object>>)
[
	{
		"status": "shipped",
		"properties": [
			{
				"values": [
					{
						"ordDt": "2024-05-14"
					}
				],
				"itemCat": 1234,
			}
		],
	}
]

The expressions on the fields at top level i.e. bMonth and bCategory were simple. The challenge comes with the nested fields in bOrdDtls, where bCustId values are needed from records meeting below conditions:

bMonth = May
bCategory = 5
bOrdDtls[n].status = shipped
bOrdDtls[n].properties[m].values[One of the values].ordDt = 2024-05-14
bOrdDtls[n].properties[m].itesmCat = 1234

With the lack of current online documentation for C# 5.3.2, the comment on aerospike-client-csharp/Framework/AerospikeClient/Exp/MapExp.cs at 5.3.X · aerospike/aerospike-client-csharp · GitHub was used to form expression as below. This expression, using MapExp.GetByKey does not seem robust either.

Any insights on forming an expression to filter bins like bOrdDtls are appreciated.

This is code written so far:

// Build expression for query filter.
Expression filterExpression = Exp.Build(
    Exp.And(
        // Apply first filter on bin bMonth (secondary index) for best performance when using
        // a secondary index.
        Exp.EQ(Exp.StringBin("bMonth"), Exp.Val("May")),
        Exp.EQ(Exp.IntBin("bCategory"), Exp.Val(5)),
		// Is this expression correct given that the first item in bOrdDtls matching the conditions has to be determined?
        Exp.EQ(MapExp.GetByKey(MapReturnType.VALUE, Exp.Type.STRING, Exp.Val(statusField), Exp.MapBin(Bin.PrefDtl)), Exp.Val(requiredStatus)),
		// What would be an expression to get the first item in bOrdDtls matching the conditions?
        Exp.GT(Form an expression here filtering bOrdDtls fields
            , Exp.Val(0))
    )
);
2

I am so sorry for the late response!!

We will need some additional information, if possible. Can we get several of the actual records from Aerospike so we can properly address the use of expressions?

You can do this easily, if you are a LINQPad user. Aerospike has a LINQPad driver. Once connected to the Aerospike DB run this command from the “C# Expression” panel: <yournamesapce>.<yoursetname>.Export("<your file path.json>");

Example: testsc.testset.Export(@"C:\SampeRecordsExported.json");

For more information about the Aerospike LINQPad driver see this blog.

If you are not a LINQPad user you can copy the output from AQL. From the AQL prompt enter:

set output json
select * from <yournamesapce>.<yoursetname> where PK = <validPK> limit 10

Again sorry for the late response and looking forward in answering your question!

BTW, the LINQPad Aerospike driver can aid in debugging expressions!

3

Hi @RAndersen, thank you for the response and sharing about the LINQPad Aerospike driver! This sure will be helpful in debugging expressions.

I am sorry, I am not unable to provide the set data, as that contains organization specific information. The example itself is a sanitized form of that data. Would be possible to assume that most of the records will have the data similar to the one provided in the example, and suggest a solution based of that assumption? Some of records may have properties field as null, which would need to be handled as well.

4

Using this put as a template and running all the code in LINQPad I was able to create your JSON structure and filter expression.

Below is the put I used:

test.Put("myset1", 6, new Dictionary<string, object>() {{"bCustId", 123},
{"bMonth", "may"},
{"bCategory", 6},
{"bOrdDtls", new object[] { new Dictionary<string,object>() {{"status","shipped"},
											{"properties", new object[]
															{ new Dictionary<string,object>()
																	{{"values",
																		new object[] {
																		new Dictionary<string,object>()
																				{{"ordDt", "2024-05-15"}}}},
																	{"itemCat", 1237}
															}}}}}}
											});

After putting several rows, I dump the set in JSON format:

test.myset1.ToJson().ToString()

[
  {
    "_id": 6,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 6,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1237,
            "values": [
              {
                "ordDt": "2024-05-15"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  },
  {
    "_id": 3,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 5,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1234,
            "values": [
              {
                "ordDt": "2024-05-15"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  },
  {
    "_id": 4,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 6,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1236,
            "values": [
              {
                "ordDt": "2024-05-15"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  },
  {
    "_id": 2,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 5,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1234,
            "values": [
              {
                "ordDt": "2024-05-14"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  },
  {
    "_id": 1,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 5,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1234,
            "values": [
              {
                "ordDt": "2024-05-14"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  },
  {
    "_id": 5,
    "bCustId": 123,
    "bMonth": "may",
    "bCategory": 6,
    "bOrdDtls": [
      {
        "properties": [
          {
            "itemCat": 1236,
            "values": [
              {
                "ordDt": "2024-05-15"
              }
            ]
          }
        ],
        "status": "shipped"
      }
    ]
  }
]

Since you have embedded arrays there is no way to “scan” these arrays using Filter Expressions. Instead you must use a index (position) into the array to obtain the value. This is done via the CTX functions. The CTX allows you to navigate nested (embedded) collections until you find the value you require.

Below is the code required to obtain the record you are looking for:

test.myset1.Query(
	Exp.And(
		// Apply first filter on bin bMonth (secondary index) for best performance when using
		// a secondary index.
		Exp.EQ(Exp.StringBin("bMonth"), Exp.Val("may")),
		Exp.EQ(Exp.IntBin("bCategory"), Exp.Val(5)),
		/*bMonth = May
			bCategory = 5
			bOrdDtls[n].status = shipped
			bOrdDtls[n].properties[m].values[One of the values].ordDt = 2024-05-14
			bOrdDtls[n].properties[m].itesmCat = 1234
		*/
		Exp.EQ(MapExp.GetByKey(MapReturnType.VALUE,Exp.Type.STRING,Exp.Val("status"),Exp.Bin("bOrdDtls",Exp.Type.LIST),
								CTX.ListIndex(0)), //Must use CTX to return first elemnt in bOrdDtls
				Exp.Val("shipped")),
		Exp.EQ(MapExp.GetByKey(MapReturnType.VALUE, Exp.Type.STRING, Exp.Val("ordDt"), Exp.Bin("bOrdDtls", Exp.Type.LIST),
								CTX.ListIndex(0), //First element in bOrdDtls list
								CTX.MapKey(Value.Get("properties")),
								CTX.ListIndex(0), //First element in list within properties
								CTX.MapKey(Value.Get("values")),
								CTX.ListIndex(0)), //return first element in values
				Exp.Val("2024-05-14")),
		Exp.EQ(MapExp.GetByKey(MapReturnType.VALUE, Exp.Type.INT, Exp.Val("itemCat"), Exp.Bin("bOrdDtls", Exp.Type.LIST),
								CTX.ListIndex(0), //First element in bOrdDtls list
								CTX.MapKey(Value.Get("properties")),
								CTX.ListIndex(0)), //return first element in properties
				Exp.Val(1234))
	)
)

Result:

image
image842Ă—949 46.4 KB

Using the Aerospike LINQPad driver you can incremental test your filter expression, one filter at a time (that is what I did).

If your data you need to query on is not in a static position within your lists, you would need to increment the list position programmability or change your “bOrdDtls” structure to use maps.

I hope I represented your data correctly and my explanation and examples make sense!

Please let me know if this was helpful and if you have any additional questions.

1 Like
5

Thank you, @RAndersen for putting so much work into creating the test data and forming the code to query data. This will be very helpful. To keep going on with with the work, I formed a solution to set a Statement.SetFilter on the secondary index bMonth, and a policy/expression on bCategory, to retrieve all bOrdDtls. I then locally looped through all bOrdDtls records retrieved from this read. Its giving the needed data, but I am not sure if it is as efficient as the solution provided by you, as your solution completely runs on the server end. Maybe my solution has some modularity. I will do a comparative run between the two and see if there’s a performance difference.

A big thank you and have a great weekend!

6

Your welcome! If you haven any additional questions, please feel free to let me know.