How to use filter on nested CDT fields

Question

Hi, 
 I have CDT-1 (Address) & CDT-2 (State). I have created a expression rule to query data. Within query filter when I am trying to filter records based on "city" which is in "state" CDT, I am getting this error below: 
 Expression evaluation error [evaluation ID = 0HJYCTXD] in rule 'rwm_activity_rule' (called by rule 'rwm_activity') at function a!queryEntity: Cannot filter by field [affectedElements.siteid] because it is a complex, multiple, or child of a multiple data type 
 filter by "personName" is working fine. Please help filter records by city (with in state CDT). 
 SAIL: 
 a!queryEntity(
 entity: cons!Address,
 query: a!query(
 logicalexpression: a!queryLogicalExpression(
 operator: "AND",
 filters: {
 a!queryFilter(
 field: "personName",
 operator: "in",
 value:ri!personName
 ),
 a!queryFilter(
 field: "state.city",
 operator: "in",
 value:ri!city
 )
 },
 ignoreFiltersWithEmptyValues: true
 ),
 selection: a!querySelection(
 columns: {
 a!forEach(
 {
 "personName",
 "state"
 },
 a!queryColumn(field: fv!item)
 )
 }
 ),
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: -1
 )
 ),
 fetchTotalCount: false
).data

Peter Lewis · Answer

Can you clarify what your data structure looks like? There are a few different reasons this might happen, and there are slightly different ways of resolving this depending on how your data is set up. 
 The error you received is happening due to one of 3 reasons: 
 
 Address to State is a 1:M relationship - I recommend against this paradigm. You will usually have better luck if you use a flat design here, which will make it easier to query the associated city first and then filter by the corresponding addresses. See the CDT Guidance page for more information. 
 City is a primitive array of fields - This paradigm is also discouraged because it makes it difficult to index fields out (pretty much exactly the issue you're seeing here). 
 City is itself a CDT - I would guess that this is not the issue. However, if this is the issue, make sure you filter by a primitive value (e.g. text, integer) and not a CDT. 
 
 The last comment I have - it seems a bit odd to me to have city nested inside of state. I usually try to use M:1 relationships instead of 1:M relationships. So in this case, you cold set it up as City M:1 State and likely have more luck with this filter.