toluaina
Need Support for Joining Postgres tables using arrays of IDs
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
