oak utils tool not working. Is there any alternative tool that will generate oak index structure

[vasim12345]

Earlier I used to use http://oakutils.appspot.com/generate/index for generating oak index. Seems like this is not working any more. Is there any other alternative tool. I was trying to generate index for following queries.

Query 1:

type=cq:Page
group.1_group.1_path=/content/wknd/us/en/magazine
group.1_group.2_path=/content/wknd/us/en/adventures
group.1_group.3_path=/content/wknd/us/en/about-us
group.1_group.p.or=true
group.2_group.path=/content/wknd/us/en/magazine/members-only
group.2_group.path.self=true
group.2_group.p.not=true
1_property=jcr:content/cq:template
1_property.value=/conf/wknd/settings/wcm/templates/content-page-template
2_property=jcr:content/tag
2_property.1_value=wknd-shared:activity/skiing1
2_property.2_value=wknd-shared:activity/skiing2
2_property.3_value=wknd-shared:activity/skiing3
orderBy=@jcr:content/publishDate
orderby.sort=dec
p.offset=0
p.limit=6

Query 2:

type=cq:Page
0_group.1_path=/content/wknd/us/en/magazine
0_group.2_path=/content/wknd/us/en/adventures
0_group.3_path=/content/wknd/us/en/about-us
0_group.p.or=true
1_group.fulltext=surfing
1_group.2_property=jcr:content/root/container/container/*/props
1_group.2_property.1_value=/content/dam/wknd/en/props/arctic-surfing
1_group.3_property=jcr:content/root/container/container/*/*/props
1_group.3_property.1_value=/content/dam/wknd/en/props/arctic-surfing
1_group.4_property=jcr:content/root/container/container/*/*/props
1_group.4_property.1_value=/content/dam/wknd/en/props/arctic-surfing
1_group.p.or=true
p.offset=0
p.limit=6
orderBy=@jcr:content/publishDate
orderby.sort=dec

Thanks in Advance.

[aanchal-sikka · Accepted solution]

hello @vasim12345 

The tool seems to work with SQL queries.

So, you first need to transform your query to SQL equivalent.

Example: First query has equivalent as

select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/content-page-template' and [jcr:content/tag] in('wknd-shared:activity/skiing3', 'wknd-shared:activity/skiing2', 'wknd-shared:activity/skiing1') and isdescendantnode(a, '/content/wknd/us/en/magazine') /* xpath: /jcr:root/content/wknd/us/en/magazine//element(*, cq:Page)[(jcr:content/@cq:template = '/conf/wknd/settings/wcm/templates/content-page-template' and (jcr:content/@tag = 'wknd-shared:activity/skiing3' or jcr:content/@tag = 'wknd-shared:activity/skiing2' or jcr:content/@tag = 'wknd-shared:activity/skiing1'))] */ union select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/content-page-template' and [jcr:content/tag] in('wknd-shared:activity/skiing3', 'wknd-shared:activity/skiing2', 'wknd-shared:activity/skiing1') and isdescendantnode(a, '/content/wknd/us/en/adventures')

To get the equivalent

1. just enable logging as per https://aemtreasury.wordpress.com/2015/11/13/how-to-enable-logging-of-aem-query/

2. Execute the query in http://localhost:4502/libs/cq/search/content/querydebug.html

3. Equivalent would be logged something like 

31.07.2023 12:21:45.812 *DEBUG* [[0:0:0:0:0:0:0:1] [1690786305738] GET /libs/cq/search/content/querydebug.html HTTP/1.1] org.apache.jackrabbit.oak.query.QueryImpl query execute select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/content-page-template' and [jcr:content/tag] in('wknd-shared:activity/skiing3', 'wknd-shared:activity/skiing2', 'wknd-shared:activity/skiing1') and isdescendantnode(a, '/content/wknd/us/en/magazine') /* xpath: /jcr:root/content/wknd/us/en/magazine//element(*, cq:Page)[(jcr:content/@cq:template = '/conf/wknd/settings/wcm/templates/content-page-template' and (jcr:content/@tag = 'wknd-shared:activity/skiing3' or jcr:content/@tag = 'wknd-shared:activity/skiing2' or jcr:content/@tag = 'wknd-shared:activity/skiing1'))] */ union select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/content-page-template' and [jcr:content/tag] in('wknd-shared:activity/skiing3', 'wknd-shared:activity/skiing2', 'wknd-shared:activity/skiing1') and isdescendantnode(a, '/content/wknd/us/en/adventures') /* xpath: /jcr:root/content/wknd/us/en/adventures//element(*, cq:Page)[(jcr:content/@cq:template = '/conf/wknd/settings/wcm/templates/content-page-template' and (jcr:content/@tag = 'wknd-shared:activity/skiing3' or jcr:content/@tag = 'wknd-shared:activity/skiing2' or jcr:content/@tag = 'wknd-shared:activity/skiing1'))] */ union select [jcr:path], [jcr:score], * from [cq:Page] as a where [jcr:content/cq:template] = '/conf/wknd/settings/wcm/templates/content-page-template' and [jcr:content/tag] in('wknd-shared:activity/skiing3', 'wknd-shared:activity/skiing2', 'wknd-shared:activity/skiing1') and isdescendantnode(a, '/content/wknd/us/en/about-us') /* xpath: /jcr:root/content/wknd/us/en/about-us//element(*, cq:Page)[(jcr:content/@cq:template = '/conf/wknd/settings/wcm/templates/content-page-template' and (jcr:content/@tag = 'wknd-shared:activity/skiing3' or jcr:content/@tag = 'wknd-shared:activity/skiing2' or jcr:content/@tag = 'wknd-shared:activity/skiing1'))] */

Please assure that the index that is generated is merged into the OOTB ones carefully. Adobe is always improving indexes. You might miss on something, if you consider only the one from the tool