Joinin DocumentDB Collections with PostgreSQL Tables

It is currently unclear whether it is possible to seamlessly join data between a DocumentDB collection and native PostgreSQL tables.

Example

Querying a DocumentDB collection:

SELECT document FROM documentdb_api.collection('documentdb', 'patient');
SET documentdb_core.bsonUseEJson TO true;
SELECT cursorPage FROM documentdb_api.find_cursor_first_page(
    'documentdb',
    '{ "find" : "patient", "filter" : {"patient_id":"P005"}}'
);

Querying a PostgreSQL table:

SELECT * FROM marie_scheduler.job;

If there is no built-in mechanism to join DocumentDB collections with PostgreSQL tables, this represents a significant limitation. PostgreSQL natively supports JSONB operations, which can achieve similar functionality within a single database, eliminating the need for an external document store.

SELECT * 
FROM marie_scheduler.job
WHERE data->'metadata'->>'doc_type' = 'doc_type'
AND data->'metadata'->>'doc_id' = 'doc_id_0001';

How can we efficiently join data between DocumentDB and PostgreSQL?

Technically you can do it and JOIN collection tables with regular PG tables, documentdb also supports ->> and -> operators on BSON types to get a particular field value but you will need to cast the extracted values to a proper type in PG (BSON supports more types than regular JSON).

e.g.

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);

INSERT INTO example_table (id, data) VALUES (1, 'First value');
INSERT INTO example_table (id, data) VALUES (2, 'Second value');

SELECT documentdb_api.create_collection('documentdb','patient');
SELECT documentdb_api.insert_one('documentdb','collection1', '{ "joinKey": 1, "data": "First"}');
SELECT documentdb_api.insert_one('documentdb','collection1', '{ "joinKey": 2, "data": "Second"}');

SELECT coll.document, tbl.data 
FROM documentdb_api.collection('documentdb','collection1') coll 
JOIN example_table tbl ON (coll.document->>'joinKey')::int = tbl.id;
                                                  document                                                  |     data     
------------------------------------------------------------------------------------------------------------+--------------
 { "_id" : { "$oid" : "67acfa68e617768fcf010d70" }, "joinKey" : { "$numberInt" : "1" }, "data" : "First" }  | First value
 { "_id" : { "$oid" : "67acfa74e617768fcf010d71" }, "joinKey" : { "$numberInt" : "2" }, "data" : "Second" } | Second value
(2 rows)

Note: The JOIN here will not be utilising any BSON indexes created on collection, again technically it is possible for you to create custom indexes with expressions to support the effective index usage of these cross table / type JOINs

@visridha

There should be a better way to do this, which could use indexes and such, but we currently restrict our aggregate functions to have a single projector. @visridha will take a look at this, should be "simple".

This should be fixed in the next sync.

Harden documentdb-local container startup and prepare the v0.116-0 re…

Sunbelt Computer Software

PL/B Language Development and Support

Navigation Menu

Repository navigation

Joinin DocumentDB Collections with PostgreSQL Tables #61

Example

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Sunbelt Computer Software

PL/B Language Development and Support

Joinin DocumentDB Collections with PostgreSQL Tables #61

Description

Example

Activity

diipak-bisht commented on Feb 12, 2025

safern commented on Feb 12, 2025

visridha commented on Feb 21, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions