So, we have an FTS index for searching texts on several fields in a collection called as content.
when we run a query like this:
SELECT SEARCH_META().id as id,
SEARCH_META().score as score,
item.`type` as `type`,
item.child_type as child_type
FROM main._default.content AS item USE INDEX(USING FTS)
WHERE module_identifier="eveolution.giltazell.philadelphia.robbanks"
AND SEARCH(item, {
"fields": [ "*" ],
"query": {
"match_phrase": "section 1"
}
});
it returns a result like following (notice that some of them are of type child and some are parent):
[
{
"child_type": "image",
"id": "c6d86655-7d3b-4d3d-9ac5-e551aa28ca84",
"score": 2.794578688163785,
"type": "child"
},
{
"child_type": "text",
"id": "bce1a9e3-0f96-4cc6-9b18-4a656a608f8d",
"score": 2.4201761802623114,
"type": "child"
},
{
"id": "00e558e8-d793-4e85-aac8-7bfbd2a68654",
"score": 2.4201761802623114,
"type": "parent"
}
]
Now, for each of the child items in the above result, I need to get the parent using the following query which uses a different global secondary index:
SELECT *
FROM `main`.`_default`.`content` AS `parent`
WHERE ANY `item` IN `parent`.`children` SATISFIES `item`.`id` = "<child-id>" END;
I am trying to do all of this in a single inner join query where I could get the parent_id along with id for child types.
Any expert guidance on this would be great.
