[mongodb-dev] Need Help : Approach to SQL,RQL Query conversion to Mongo-JAVA

[h***@gmail.com]

Hi Team,

Thanks for the time if you happen to go thru this post

Am pretty new to Mongo-Java development space and need some pointers on
getting thru this .

Have designed MongoDB to host the following collections with Product and
Sku being the crux , as part of data modelling

1. Product
2. Sku
3. Loyalty etc

Now I have an SQL query similar to below one : The purpose of this query
is to set a field *(NewComp*) ON/OFF depending on the output of the query

Select NewComp =

Select distinct a.product_id , a.display_name,b.
family_id,e.sku_id,e.First_Onsite


From dcs_product a , www_ss_product b ,
dcs_prd_chldsku c , dcs_sku d , www_sku e , www_prod_content f ,
www_sku_prop g


Where
a.prod_id = b . prod_id and b.prod_id
= c.prod_id // all the prod tables of SQL are moved to single Product
collection and similar approach to Sku as well
c.sku_id = d.sku_id AND d.sku_id = e
.sku_id
AND f.prod_id = b.prod_id

AND c.sku_id = g.sku_id
AND g.height > 0 AND g.weight > 0 AND g
.depth > 0 AND g.width > 0
AND g.weight_unit_id IS NOT NULL
AND b.tax_code > 0 AND b.complete !=1
AND ( d.list_price > 0 or e.web_exclusive
= 2 )
AND c.seq_num = 0
AND (e.web_exclusive = 3 or ( ( e.fulfiller
IN (1,2,3,4) and e.prod_cost > 0 and (d.list_price > e. prod_cost or e.web_exclusive
=2 ))

OR ( e.fulfiller = 0 and e.ds_prod_cost IS NOT NULL and e.ds_prod_cost

0 and ( d.list_price > e. prod_cost or e.web_exclusive =2) ) ) )

AND e.status = 1 and b.brand IS NOT
NULL and e.fulfiler IS NOT NULL a.display IS NOT NULL and b.subbrand IS NOT
NULL AND select ( (count (dcs.sku_id) from dcs_sku_aux dcs where dcs.sku_id
= e.sku_id ) > 0)
AND ( NOT regexp_like (a.display , '[\
\ \ \ ~ @ ^|_?<>=]' , 'n' ) ) AND a,parent_cat IS NOT NULL and a,parent_cat
in (SELECT cat_id from dcs_child where chld_prd = a.prod_id )
AND (e.fulfiler ! = 4 and e.vendor_id
IS NOT NULL )
AND ( (e.overide_vendor_id IS NULL or e
.overide_vendor_id = 0) or ( e. active_src IS NOT NULL and select ( (count (
sku_cost ) from www_sku_cost where vendor_id IS NOT NULL and sku_cost =
e.active_src) > 0 ) ) )
AND ( e.web_exclusive IN ( 2,3) or ( e.fulfiller
!= 0 or e.ds_sku_id IS NOT NULL) and ( e.web_exclusive IN (1,2) or ( (
select count (0) from www_price g where g.wic = e.wic ) > 0 ) and (
select count (0) from brand where b.brand = h.brand
) > 0 ) AND e.wic NOT IN [ {wic list}]

I am trying to build the similar query on the Mongo with out much luck ,
not sure if I need to use *'Aggregation/Filter'* classes of java driver to
build this query using join (lookup) or if I can use the *'Query'* class
and pass these fields as part of the Criteria like below , Need some
guidance on the same

Query query = new Query () ;
query.addCriteria ( new Criteria() . where ( chain the AND statements in
the SQL) )

finally pass this query object to find method - find ( query ,
entity.class) // When using the above constructs how to use the join/lookup
and also how to go about the nested select statements)

Appreciate your time and support. Thanks