Usage of SQL2 distinct is not returning unique values

[shady-zabady]

Hey all,

I have the following SQL2 query where I am retrieving documents that have an associated notification. Users have notification nodes under their home path directory, with a documentPath attribute containing the path to the document. Users can have multiple notification nodes referencing the same document path. Is there a way to retrieve the list of unique document paths (no duplicates) of these documents?

Currently, the below simple query will return duplicate documents if there are multiple notification nodes referencing the same document. I thought DISTINCT would mitigate that unless I am misunderstanding the spec?

SELECT DISTINCT document.[jcr:path] from [cq:Page] as document
INNER JOIN [nt:unstructured] as notification on document.[jcr:path] = notification.[documentPath]
where isdescendantnode(document, '/content/experience-fragments/documents') and isdescendantnode(notification, '/home/users/bluh')

The reason I would like the uniqueness to occur in oak is because I will be ordering the documents according to a specific property, so sorting in java will require returning the entire set of documents which is not feasible.

EDIT: CRXDE omits duplicates on the Javascript level which is confusing when testing the query since the result actually does contain duplicate documents! – You can validate that by observing the sentence that says “# results” under the list of nodes is larger than the number of nodes presented.  (not 2)

Or by checking the JSON response after hitting the “execute” button. The best way to test this is to use the QueryManager API directly.

[arunpatidar]

I think it should work as per the document https://docs.jboss.org/modeshape/2.7.0.Final/manuals/gettingstarted/html/jcr-query-and-search.html#j...

I also tried and it does work

e.g.

SELECT DISTINCT document.[jcr:path] from [nt:unstructured] as document
INNER JOIN [nt:unstructured] as notification on document.[jcr:path] = notification.[path]
where isdescendantnode(document, '/tmp/content/dam') and isdescendantnode(notification, '/tmp/content/dmsearch')

I have 2 page nodes page1 and page2 at /tmp/content/dam and 3 task nodes at /tmp/content/dmsearch with property path, which refers either of the page.

[shady-zabady]

This is not the case because CRXDE filters the results on the JS level to omit duplicates. You can validate that by observing the sentence that says “# results” under the list of nodes is larger than the number of nodes presented, or by checking the JSON response after hitting the “execute” button. The best way to test this I think is to use the QueryManager API. Thanks so much for checking 🙂