How to apply aggregate functions(like MIN, MAX, COUNT) in JCR-SQL2?

3
votes

I have some records stored as Nodes in JCR and the name of the node is the primary key. eg 1,2,3. But the problem starts here,

the records are as follows 1,2,6,53,54

Where the numbers above are nodes under EMP unstructured node. If I do

int count=empNode.getNodeIterator().getSize() I will get 5 As there are 5 nodes

So I do count++ which gives me 6 but 6 already exists, so I can't create a node with name 6 under EMP[nt:unstructred], thats why I want to apply MAX(nodeNames) something in the query. What should I do ?

Update :: I use CQ5.5. EMP is an unstructered node under content like /content/EMP. Under this(EMP) I have unstructered nodes that hold my data. And these node have names as 1,2, etc

2
javaaemjcrjcr-sql2
Which version of CQ5 do you use? And what is 'EMP'? – Reporter
@reporter :: Please see the update – Oliver
See my answer below. – Reporter
Possible duplicate of Jackrabbit searches across joined nodes – Paul Sweatte

2 Answers

3
votes

I tried with my CQ5.4 instance to find a soloution. Unfortunatly my tries were not successful. When I used the keywords 'sql2 count' with Google, I found this page. There was asked the same question and the answer was

There is no count(*) or group by selector in JCR SQL 1, XPath [2] or JCR-SQL2/AQM [3].

To implement such a tag cloud, you can run one query that fetches all your content containing the relevant "tag" property:

//element(*, my:Article)[@tag]

and then iterate over the result and count your tags on the application side by looking at the tag property values and using some hashmap (tagid -> count).

http://www.day.com/specs/jcr/1.0/ (section 8.5)

http://www.day.com/specs/jcr/1.0/ (section 6.6)

http://www.day.com/specs/jcr/2.0/6_Query.html

I think you can connect this answer to MAX() and MIN().

0
votes

I implemented a simple Apache Sling servlet to implement the count(*) function. More information here: https://github.com/artika4biz/sling-utils