JCR SQL2 query for finding list of tags with missing property

[tushaar_srivastava]

Hi Team,

I have the list of tags under /content/cq:tags/project
Every tag should have a property called guid [a custom property just holds UUID].

But due to some reason, many tags do not have guid and I need to get the list out of it.

example:

I am trying to use the ACS Commons report, but my query always shows java.text.ParseException I am trying to use Fulltext conditions 
https://experienceleague.adobe.com/docs/experience-manager-65/assets/JCR_query_cheatsheet-v1.1.pdf

Can anyone please suggest me the correct way of writing the JCR SQL2 query to get the list of tags which does not have guid property,

Thanks.

@kautuk_sahni  @arunpatidar  @lukasz-m 

[lukasz-m · Accepted solution]

Hi @tushaar_srivastava 

You can try this SQL2 query, it should return all tags from given path that does not have guid property at all (s.[guid] IS NULL) or guid property is empty (s.[guid] = "").

SELECT * FROM [cq:Tag] AS s WHERE ISDESCENDANTNODE([/content/cq:tags/we-retail]) AND (s.[guid] IS NULL OR s.[guid] = "")

[tushaar_srivastava]

Thank you @lukasz-m , I was trying SQL query instead of SQL2.
Thank you for guiding me here with the correct syntax