SQl2 query with contains does not find any nodes | Community
Skip to main content
Magicr
Magicr
Level 6
September 22, 2020
Solved

SQl2 query with contains does not find any nodes

  • September 22, 2020
  • 5 replies
  • 9729 views

Hello,

I'm facing following strange situation and I have no idea about this behavior, using AEM6.4. Given is following situation:

 

In my project exists some nodes they have in property 'sling:resourceType' the value "c109_video". So I created two queries to find those nodes.

Query one: 

SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND n.[sling:resourceType] like 'c109_video' ORDER BY n.[jcr:created] ASC

The result list contains more than zero hits. 

 

Query two: 

SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND CONTAINS(n.[sling:resourceType], 'c109_video') ORDER BY n.[jcr:created] ASC

 The result list is empty.

 

Which reasons are responsible for this difference?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Magicr

Some associates there works also with AEM faced the same situation. They have also no explainnations for this strange behavior. They think there are not documented changes in Service Pack 6, so thier recommendation is using like operator instead of contains

5 replies

A
Adobe Employee
aemmarc2Adobe Employee
Adobe Employee
September 22, 2020

CONTAINS and LIKE do two different things.

 

    A
    Adobe Employee
    aemmarc2Adobe Employee
    Adobe Employee
    September 22, 2020
    https://docs.adobe.com/docs/en/spec/jcr/1.0/6.6.5.1_jcr_like_Function.html
    A
    abhilakhpatre1
    Level 2
    September 22, 2020

    Your first query looks fine and gives a expected results. If you want to search with like clause you can also introduce wildcard characters based upon your requirements to match the words (trailing or starting)

     

    When it comes to CONTAINS , it will also work for non-delimetered words (simplewords) for it to be worked for delimited words like in your case _ underscore, you have to add double quotes to the text you are searching. I have rephrased your query with double quotes like below :

     

    SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND CONTAINS(n.[sling:resourceType], “'c109_video'”) ORDER BY n.[jcr:created] ASC

     

    For more details you can also refere below links to understand the difference:

    https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/difference-between-contains-and-like-clauses-in-search/qaq-p/195402

     

    https://stackoverflow.com/questions/48077571/why-does-contains-find-inequal-text-strings-in-jcr-sql2

    Hope this might be helpful for you 🙂

      Magicr
      MagicrAuthor
      Level 6
      September 23, 2020

      Thanks for trying to help. I tried your suggested query and the result list is still empty. 

      Update: It seems only the property jcr:title returns a non empty resultlist. What  a hell?

      Jineet_Vora
      Community Advisor and Adobe Champion
      Jineet_VoraCommunity Advisor and Adobe Champion
      Community Advisor and Adobe Champion
      September 22, 2020

      @magicr,

      Difference between Contains and Like:
      1. Contains: Searches the given string anywhere in the data

      2. Like: Works with regular expression and searches for an expression. e.g. %adobe%

        Magicr
        MagicrAuthor
        Level 6
        September 24, 2020
        @jineet_vora I think you haven't understood my question right. And I haven't marked this as solution (whoever it was).
        Magicr
        MagicrAuthorAccepted solution
        Level 6
        October 2, 2020

        Some associates there works also with AEM faced the same situation. They have also no explainnations for this strange behavior. They think there are not documented changes in Service Pack 6, so thier recommendation is using like operator instead of contains

          S
          sarikajain
          Level 2
          March 29, 2023

          I am facing similar issue. I suspected that the only possible explanation for this behavior, is that contains performs a fulltext search and hence requires to be backed up by an index while like performs simple String comparison. I looked for some documentation to confirm on my doubt and I found the exact link I was looking for. If you are still looking for an answer, here it is:

          https://developer.adobe.com/experience-manager/reference-materials/spec/jcr/1.0/6.6.3.3_Property_Constraint.html

          Support for the jcr:contains() (CONTAINS() in SQL) function is not required for any property types in particular. It is however required to work at the node level. In that case it applies to those properties of the node for which the implementation maintains an index. 

          I have only one doubt left though. I hope someone is able to answer it for me. The contains function works for custom properties and jcr:title but not for other properties in jcr, sling and cq namespace and which oak:index node should I refer for check which index is being used by the contains function. 

           

             

          Terms & ConditionsAccessibility statement

          Loading footer...