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

**URL:** <https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819>\
**Category:** C# Client\
**Tags:** query, secondary, filter, index\
**Created:** [November 20, 2024, 8:14pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819 "2024-11-20T20:14:37Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![G\_Singh](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/g_singh/32/2497_2.png) [@G\_Singh](https://discuss.aerospike.com/u/G_Singh)\
**Post date:** [November 20, 2024, 8:14pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/1 "2024-11-20T20:14:37Z")

</div>

Hi there,

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

```auto
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:

```auto
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](https://github.com/aerospike/aerospike-client-csharp/blob/5.3.X/Framework/AerospikeClient/Exp/MapExp.cs#L392)_ 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:

```auto
// 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))
    )
);

```

---

<div class="post-metadata">

**Author:** ![RAndersen](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/randersen/32/2260_2.png) [@RAndersen](https://discuss.aerospike.com/u/RAndersen)\
**Post date:** [December 4, 2024, 10:44pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/2 "2024-12-04T22:44:22Z")

</div>

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](https://www.linqpad.net/) 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](https://aerospike.com/blog/aerospike-for-linqpad/).

If you are not a LINQPad user you can copy the output from [AQL](https://aerospike.com/docs/tools/aql). From the AQL prompt enter:

```auto
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!

---

<div class="post-metadata">

**Author:** ![G\_Singh](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/g_singh/32/2497_2.png) [@G\_Singh](https://discuss.aerospike.com/u/G_Singh)\
**Post date:** [December 5, 2024, 1:56am UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/3 "2024-12-05T01:56:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RAndersen](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/randersen/32/2260_2.png) [@RAndersen](https://discuss.aerospike.com/u/RAndersen)\
**Post date:** [December 5, 2024, 10:48pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/4 "2024-12-05T22:48:46Z")

</div>

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:

```auto
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()`

```auto
[
  {
    "_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:

```auto
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](https://us1.discourse-cdn.com/flex019/uploads/aerospike/original/2X/c/cfda52858ffb53f4529f0b0b7a4fff70546eff77.png)

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.

---

<div class="post-metadata">

**Author:** ![G\_Singh](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/g_singh/32/2497_2.png) [@G\_Singh](https://discuss.aerospike.com/u/G_Singh)\
**Post date:** [December 6, 2024, 11:56pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/5 "2024-12-06T23:56:38Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![RAndersen](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/randersen/32/2260_2.png) [@RAndersen](https://discuss.aerospike.com/u/RAndersen)\
**Post date:** [December 9, 2024, 8:15pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/6 "2024-12-09T20:15:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/system/32/2274_2.png) [@system](https://discuss.aerospike.com/u/system)\
**Post date:** [December 9, 2025, 8:15pm UTC](https://discuss.aerospike.com/t/filter-expression-formation-for-a-nested-map-field-c-aerospike-client-5-3-2/11819/7 "2025-12-09T20:15:44Z")

</div>

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.