Get results from flexiblesearch as column not raw in order to process it further.

[9999999]

I have a query which retrives categories for product :

select {code} from {category} where {PK} IN
({{select {level2.target},{level1.target},{level0.target},{cat.PK} from
 {Product as prod
     join CategoryProductRelation as level3 on {level3.target} = {prod.pk}
     join CategoryCategoryRelation as level2 on {level2.target} = {level3.source}
     join CategoryCategoryRelation as level1 on {level1.target} = {level2.source}
     join CategoryCategoryRelation as level0 on {level0.target} = {level1.source}
     join Category as cat on {cat.pk} = {level0.source}
 }
 where {prod.code} = '000000000002753535'}})

and it doesn't work since categories are returned as a raw not a column (gives error in HAC:
Operand should contain 1 column(s) ).
Inner query works fine and returns results, however it is returned as row .

Does someone know how to write such a query in order to get all categories just through the one query ?

[9999999]

It could be achived by this query

private static final String SELECT_ALL_CATEGORY_CODES_FOR_PRODUCT = "SELECT {code} FROM {Category} WHERE {PK} IN " +
    "({{SELECT {level2.target} FROM {Product AS prod JOIN CategoryProductRelation AS level3 ON {level3.target} = {prod.pk} " +
    "JOIN CategoryCategoryRelation AS level2 ON {level2.target} = {level3.source}} WHERE {prod.pk} =?productPk}} " +
    "UNION " +
    "{{SELECT {level1.target} FROM {Product AS prod JOIN CategoryProductRelation AS level3 ON {level3.target} = {prod.pk} " +
    "JOIN CategoryCategoryRelation AS level2 ON {level2.target} = {level3.source} " +
    "JOIN CategoryCategoryRelation AS level1 ON {level1.target} = {level2.source}} WHERE {prod.pk} = ?productPk}} " +
    "UNION " +
    "{{SELECT {level0.target} FROM {Product AS prod JOIN CategoryProductRelation AS level3 ON {level3.target} = {prod.pk} " +
    "JOIN CategoryCategoryRelation AS level2 ON {level2.target} = {level3.source} " +
    "JOIN CategoryCategoryRelation AS level1 ON {level1.target} = {level2.source} " +
    "JOIN CategoryCategoryRelation AS level0 ON {level0.target} = {level1.source}} WHERE {prod.pk} = ?productPk}} " +
    "UNION " +
    "{{SELECT {cat.PK} FROM {Product AS prod JOIN CategoryProductRelation AS level3 ON {level3.target} = {prod.pk} " +
    "JOIN CategoryCategoryRelation AS level2 ON {level2.target} = {level3.source} " +
    "JOIN CategoryCategoryRelation AS level1 ON {level1.target} = {level2.source} " +
    "JOIN CategoryCategoryRelation AS level0 ON {level0.target} = {level1.source} " +
    "JOIN Category AS cat ON {cat.pk} = {level0.source}} WHERE {prod.pk} = ?productPk}})"

However it is very inefficient.