如何无需拼接将PostgreSQL数据转换为MongoDB复杂对象？

Hey there! I totally get why you want to skip messy string concatenation for nested JSON—it’s error-prone (especially with special characters) and a nightmare to maintain. Good news: PostgreSQL has robust built-in JSON functions that make this straightforward and clean.

Core Approach: Use json_build_object() with Nested Constructs #

The json_build_object() function lets you construct JSON objects by defining key-value pairs directly. You can nest other JSON tools like row_to_json() (to convert table rows to JSON objects) or even more json_build_object() calls for deep nested structures—no string拼接 required.

Example for Your Target JSON Structure #

Let’s build exactly the nested JSON you provided. Here are two common scenarios:

Scenario 1: _ownedByCred data comes from a related table

Suppose your operation table links to a cred table (e.g., via an ope_cre_id foreign key). You can join them and convert both rows to JSON seamlessly:

SELECT json_build_object(
    'operation', row_to_json(op.*),
    '_ownedByCred', row_to_json(cred.*),
    'exampleGroup', json_build_object(
        'exampleSubGroup', json_build_object(
            'data1', 'teste',
            'data2', 'teste'
        )
    )
) AS mongo_json
FROM operation op
JOIN cred ON op.ope_cre_id = cred.cre_id;

Scenario 2: _ownedByCred is a fixed value or manually derived

If you don’t need to join another table, just construct that object directly:

SELECT json_build_object(
    'operation', row_to_json(record),
    '_ownedByCred', json_build_object('cre_id', 1, 'cre_name', 'someName'),
    'exampleGroup', json_build_object(
        'exampleSubGroup', json_build_object('data1', 'teste', 'data2', 'teste')
    )
) AS mongo_json
FROM (
    SELECT ope_id, ope_value FROM operation
) AS record;

Why This Beats String Concatenation #

• Automatic Escaping: PostgreSQL handles special characters (quotes, backslashes, etc.) in your data automatically—something string拼接 would break instantly.
• Readability: You can clearly see the JSON structure right in the SQL, making it easy to tweak later.
• Flexibility: Swap between json and jsonb (use jsonb_build_object() instead) for better performance with large datasets, since JSONB is PostgreSQL’s binary format (closer to MongoDB’s BSON).

内容的提问来源于stack exchange，提问作者Victor Augusto