8 Replies Latest reply on Nov 13, 2018 4:39 AM by kautuksahni

    query builder query to find property and value

    anuj.pathak Level 1

      I am trying to find a way to search for a particular property and check if its value exceeding certain characters and print the complete node path where that property exists along with property value.

       

      I was able to get the path detail using CRXDE sql 2 query like this

       

      SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('/content/us') AND (p.[videoId] <> '' AND LENGTH(p.[videoId])>11)

       

      Can someone help me converting this to query builder query?

       

      I tried these but looks like it is returning everything

       

      http://localhost:4502/bin/querybuilder.json?p.hits=full&type=nt:unstructured&nodename=vide oId&path=/content/us

                                             or

      http://localhost:4502/bin/querybuilder.json?path=/content/us&property=@jcr:videoId&Length( property.value)&gt;11&p.hits=full&p.limit=-1

       

       

       

      desktop_exl_promo_600x100_weknowyou.png

        • 1. Re: query builder query to find property and value
          Veena_07 Experience Cloud MVP

          If the SQL 2 query is working for you ; why are you looking for a query-builder query ? There wont be any kind of performance impact if you use any of these

          • 2. Re: query builder query to find property and value
            Veena_07 Experience Cloud MVP

            Still to answer your question

             

                As far as my memory serves me right, there is no direct query in Query Builder. The max what you can do is to fetch the property nodes which you are searching and on the result set , you check the length of the values and print those nodes which meets your criteria .

             

            JcrPropertyPredicateEvaluator ("The Adobe AEM Quickstart and Web Application.") will tell you what all properties are available for Property Predicate

             

             

             

            Lets say I want to search for nodes which has a property = textIsRich under /content/geomtrixx. Then I would write a query like below

             

            path=/content/geometrixx

            type=nt:unstructured

            property=textIsRich

            property.operation=exists

             

             

                 Now I will write this query in my Java class and when the result set is returned , I will run through the values of this property and check for the length of the values and store those paths in a collection accordingly

             

             

            // create query description as hash map (simplest way, same as form post)

                Map<String, String> map = new HashMap<String, String>();

              

            // create query description as hash map (simplest way, same as form post)

                map.put("path", "/content/geometrixx");

                map.put("type", "nt:unstructured");

                map.put("property", "textIsRich");

                map.put("property.operation", "exists");

               Query query = builder.createQuery(PredicateGroup.create(map), session);

                SearchResult result = query.getResult();

             

                 // iterating over the results

                for (Hit hit : result.getHits()) {

                   String path = hit.getPath();

                   Resource resource = resourceResolver.getResource(path);

                    Node node = resource.adaptTo(Node.class);

                    // read property values

                    String textIsRich = node.getProperty("textIsRIch").getString();

             

                    //write the logic to check the length and then if it matches add that to a collection or write to a file

             

             

                }

            • 3. Re: query builder query to find property and value
              anuj.pathak Level 1

              thanks for your reply but i need some other information which are not coming out using sql and i can not export result set using CRX

              • 4. Re: query builder query to find property and value
                Veena_07 Experience Cloud MVP

                Can you let me know what exactly you are looking for ? May be we can help?

                • 5. Re: query builder query to find property and value
                  anuj.pathak Level 1

                  Thanks for your generosity.

                  can you help me updating my sql query to print videoId property value as well ?

                  doesn't matter , what i write but that sql is returning only full JCR path where that property exits but i need exact value of videoId property as second column.

                  for the export result issue, i will able to find workaround using chrome with little manual work but its ok for now.

                  • 6. Re: query builder query to find property and value
                    Veena_07 Experience Cloud MVP

                      for (Hit hit : result.getHits()) {

                          String path = hit.getPath();

                          Resource resource = resourceResolver.getResource(path);

                            Node node = resource.adaptTo(Node.class);

                            // read property values

                            String videoIdValue= node.getProperty("videoId").getString();

                     

                            //write the logic to check the length and then if it matches add that to a collection or write to a file

                     

                     

                        }

                     

                     

                    I can help you with the QueryBuilder API . Can you try like this ? This should fetch the value of the property.

                    • 7. Re: query builder query to find property and value
                      anuj.pathak Level 1

                      oh i know it will be fairly easy to get this through java class but unfortunately writing jive code is not an option for me and if there is no way to check property length in query builder query parameter , i will like to work on fixing sql query to print node path and property value where my condition returns true.

                      • 8. Re: query builder query to find property and value
                        kautuksahni Adobe Employee

                        AEM Query Structure:- Queries are always the backbone of any structure and from performance point of view. It becomes extremely important to write most optimized query. Lets decompose a query and see what all it consist of :-

                        • Predicates – If no parameter is provided, predicate type is mirrored in final query
                        • Parameter – Predicate Parameter
                        • Value – Value of predicate

                         

                        Standard Predicates : Deep understanding of predicates is necessary if you want to Optimize any if your Search Query.

                        • path : This is used to search under a  particular hierarchy only.
                          • path.self=true : If true searches the subtree including the main node given in path,  if false searches the subtree only.
                          • path.exact=true : If true exact path is matched, if false all descendants are included.
                          • path.flat=true : If true searches only the direct children .
                        • type: It is used for searching for a  particular nodetype only.
                        • property: This is used to search for a specific property only.
                          • property.value : the property value to search . Mutilple values of a particular property could be given using property.N_value=X , where N is number from 1 to N.
                          • property.depth : The number of additional levels to search under a node. eg. if property.depth=2 then the property is searched under
                            1
                            (@jcr:title = 'foo'or */@jcr:title = 'foo'or */*/@jcr:title = 'foo')
                            • property.and : If multiple properties are present , by default an OR operator is applied. If you want an AND , you may use property.and=true
                            • property.operation :equals” for exact match (default), “unequals” for unequality comparison, “like” for using the jcr:like xpath function , “not” for no match , (value param will be ignored) or “exists” for existence match .(value can be true – property must exist).

                        Good Reference Read:- https://hashimkhan.in/2015/12/02/query-builder/ , http://www.aemcq5tutorials.com/tutorials/adobe-aem-cq5-tutorials/aem-query-builder/ and Query Builder API