Need Support for Joining Postgres tables using arrays of IDs

[kundankumarcasio]

PGSync version: latest

Postgres version: 15.2

Elasticsearch/OpenSearch version: 7.17.13

Redis version: 7.2

Python version: 3.8

Problem Description:
I want to index children with given condition
[
{
"database": "databaseName",
"index": "indexName",
"nodes": {
"table": "parentTable",
"columns": [],
"children": [
{
"table": "childTable",
"columns": [],
"relationship": {
"variant": "object",
"type": "one_to_many",
"in": { //need help here
"child": [
"id"
],
"parent": [
"childTable_ids"
],

}
}
}
]
}
}
]

Equivalent SQL Query:
select * from parentTable join childTable where childTable.id=ANY(parentTable.childTable_ids)

table Structure

Parent Table
id[serial],childTable_ids[serial Array],...
1,{1,2,3,4},....

Child Table
id[serial],other cols..
1,data1,data2..
2,....,
3,....
4,.....
5,.....

Error Message (if any):

[jvanderen1]

PGSync is probably not a good use case for this. I would reconsider either how you should index your cluster or change your Elasticsearch queries to search on the correct conditions.

[toluaina]

Yes, I agree with the comment above. It would be non-trivial to generalize for this use case.
Maybe consider indexing the data and using transformations to manipulate the data in ES/OS