Aggregation not working with MongoDB

[rockingA]

I am using Kundera with MongoDB here is example

& I am trying to give Query

String minQuery = "Select min(p.price) from Product p";

Query query = em.createQuery(minQuery); Double output = query.getResultList().get(0); I am getting error

Product cannot be casted to Double what is I am missing ??

[devender-yadav]

Hi @rockingA

Have you added <property name="kundera.indexer.class" value="com.impetus.client.es.index.ESIndexer"/> property in your persistence.xml and @IndexCollection(columns = { @com.impetus.kundera.index.Index(name = "price") }) on Product entity?

-Dev

[rockingA]

Hi @devender-yadav ,

Thanks for your reply,

Do i have to Index every column which we suppose to aggregate ? Which NoSQL DB supports Direct aggregate ?

I am facing 2 issues

1. while selecting specific attributes in JPQL Query, it should return Object[], but i returns POJO TypedQuery<Object[]> query = entityManager.createQuery(queryString, Object[].class)

But this returns Actual Object which is Product.

2. I am dynamically creating two types of queries, Aggregate Query & select query with specific attributes

@Override
    public Object getTransactionAggregationOverDateRange(AggregateStrategy aggregationStrategy, String aggregateOn, String whereClause, Map<String, Object> valuesMap, Date startDate, Date endDate, String tenantId, EntityManager entityManager) {
        Double transactions=0.0;

        String queryString = "SELECT " + aggregationStrategy.getFunctionName() + "(e." + aggregateOn + ") FROM Transaction e WHERE e.transactionDate between :startDate and :endDate";
        if (null != whereClause) {
            queryString += " and " + whereClause;
        }
        System.out.println("<MyLOGS : " + queryString);
        Query query = entityManager.createQuery(queryString).setParameter("startDate", startDate).setParameter("endDate", endDate);
        for (Map.Entry<String, Object> dataEntry: valuesMap.entrySet()) {
            System.out.println("<MyLOGS : [DATA_FROM_MAP] => (" + dataEntry.getKey() + ", " + dataEntry.getValue() + ")");
            query.setParameter(dataEntry.getKey(), dataEntry.getValue());
        }

        Object queryResult = query.getSingleResult();
        if (null != queryResult) {
            if (queryResult instanceof Long) {
                transactions = ((Long)queryResult).doubleValue();
            } else if (queryResult instanceof Float) {
                transactions = ((Float)queryResult).doubleValue();
            } else {
                transactions = (double) queryResult;
            }
        }
        return transactions;
    }

    @Override
    public Object[] getTransactiontByAdhocCriteria(String selectClause, String whereClause, Map<String, Object> valuesMap, Date startDate, Date endDate, String tenantId, EntityManager entityManager) {
        String queryString = "SELECT " + selectClause + " FROM Transaction e WHERE e.transactionDate between :startDate and :endDate";
        if (null != whereClause && whereClause.length() > 0) {
            queryString += " and " + whereClause;
        }
        System.out.println("<MyLOGS : " + queryString);
        TypedQuery<Object[]> query = entityManager.createQuery(queryString, Object[].class).setParameter("startDate", startDate).setParameter("endDate", endDate);
        for (Map.Entry<String, Object> dataEntry: valuesMap.entrySet()) {
            System.out.println("<MyLOGS : [DATA_FROM_MAP] => (" + dataEntry.getKey() + ", " + dataEntry.getValue() + ")");
            query.setParameter(dataEntry.getKey(), dataEntry.getValue());
        }
        return query.getResultList().get(0);
    }

Both of above works in normal MySQL JPA. It doesn't work in Kundera-Hbase, Kundera-Cassandra, Kundera-MongoDB

In my case I can use any NoSQL DB which supports same JPA implementation

[devender-yadav]

Hi @rockingA,

Aggregation in Kundera is supported in 2 ways:

• Using native query
• Using Elasticsearch as indexer

If you know how to write a query in NoSQL database, you can go with the 1st option.

For example, in cassandra:

String qry = "Select max(\"AGE\") from \"PERSONCASSANDRA\"";
Query q = entityManager.createNativeQuery(qry);
List persons = q.getResultList();

If you want to use JPQL, go with the 2nd option. You need to add kundera-elastic-search dependency in your project.

<dependency>
    <groupId>com.impetus.kundera.client</groupId>
    <artifactId>kundera-elastic-search</artifactId>
    <version>3.7</version>
</dependency>

Yes, you have to Index every column on which you want to perform aggregation.

Also, as I mentioned above:

Add <property name="kundera.indexer.class" value="com.impetus.client.es.index.ESIndexer"/> property in your persistence.xml and @IndexCollection(columns = { @com.impetus.kundera.index.Index(name = "price") }) on the entity.

-Dev

[rockingA]

Hi @devender-yadav ,

Thanks for your reply.

Can you suggest me with JPA based (without writing native query or Using ES) implementation of Mongo / CASSANDRA where Aggregation & selective search is possible

[devender-yadav]

Hi @rockingA,

Without writing native query or Using ES it's not possible to perform aggregation using Kundera.

-Dev

[rockingA]

Hi @devender-yadav ,

Is there any JPA other implementations (Other than Kundera) that supports my use-case

[devender-yadav]

Hi @rockingA,

I am not very much aware of other JPA implementors. You can search for Spring Data, Hibernate OGM, etc. (I have not tried these tools myself).

-Dev