Don't want duplicate entries from query

[Abhishekty]

Hi, 

I have an existing query and I have updated it with one filter but now I am getting duplicate results. I don't want duplicate entries.

Query: 

 SELECT * FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/myProject/en/devices])) and s.derivedInventoryStatus = 'Back Order' and s.availabilityStatus = 'Sellable'

for unique results, do I need to update any properties for the below nodes ?

1. /oak:index/lucene/indexRules/nt:base/properties/derivedInventoryStatus

2. /oak:index/lucene/indexRules/nt:base/properties/availabilityStatus

[arunpatidar]

Unfortunately, there are no aggregation functions in JCR SQL|XPath|JCR-SQL2. The only thing I can suggest you, is to iterate over these nodes and do all calculations or put path in SET in your code

[Vijayalakshmi_S]

Hi @Abhishekty

Could you please let know

• your existing query (original query)
• additional filter that you are referring to (which results in duplicate results)
• AEM instance version

[Abhishekty]

@Vijayalakshmi_S , Thanks for reply.

Please my inline comments : 

• your existing query (original query) : SELECT * FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/myProject/en/devices])) and s.availabilityStatus = 'Sellable'
• additional filter that you are referring to (which results in duplicate results): s.derivedInventoryStatus = 'Back Order'    One thing I have observed is if I have copy-pasted the same device (on etc/commerce/products/myProject/in/devices) then also I am getting both node data with the original query. Can we isolate the result with some unique key (as currently, it has
  ensembleId as unique key)
  •                                                                I am thinking if in AEM also we can apply DISTINCT for unique results similar to SQL. 
• AEM instance version Adobe Experience Manager 6.5.6.0

[Vijayalakshmi_S · Accepted solution]

Thanks for your inputs @Abhishekty 

We can create Property Index which allows for unique constraint on respective property. In this case, we can create property index for the property named ensembleId.

With this in place, it won't let you copy the node at first place/to have a node with duplicate ensembleId (Unique constraint violation exception would be thrown)

OOTB property index definition with unique constraint support - 

• /oak:index/uuid
• /oak:index/authorizableId

You can refer the same and create one for ensembleId (declaringNodeTypes - nt:unstructured, propertyNames - ensembleId)