Advanced Sync Rule Query skip due Table not having primary key

Elastic Stack Elasticsearch
1

Hello !

Basically what the title says. I have everything working fine, i can ingest data and all with big queries and JOINS.
The "issue" i'm having is with a particular table that i need to use in one of my queries. That table has not a primary key per se.

Here is a short example of the query that is usefull for this question:

SELECT
p.Id,
p.Name,
p.Description,
STRING_AGG(pt.TagId, ',') AS Tags
FROM
Products p
LEFT JOIN
ProductTags pt ON p.Id = pt.ProductId
WHERE
p.IsDeleted = 0
GROUP BY
p.Id,
p.Name,
p.Description

This ProductTags table is actually a table to bind a elements from a Tags table with elements from Product table, basically each row of that table is a combined primary key, not having a primary key per row as an individual number. (productId, tagId).

The idea is that the document that gets created from the product, has a Tags array with all the tags related to it in the db.

So, the query is being skipped due to ProductTags not having a PK column. Is there a way this can be managed? Or the only solution is to add an Id column to each of the ProductTags table?

Thanks!