Need Support for Joining Postgres tables using arrays of IDs
kundankumarcasio opened this issue · 2 comments
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):
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.
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