Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Don't want duplicate entries from query

Avatar

Level 3

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

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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)

View solution in original post

0 Replies

Avatar

Community Advisor

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

Avatar

Community Advisor

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

 

Avatar

Level 3

@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)
    • Screenshot 2021-12-23 at 4.47.59 AM.pngScreenshot 2021-12-23 at 5.06.07 AM.png                                                               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

Avatar

Correct answer by
Community Advisor

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)