omni_json

pig build pkg omnigres;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install omnigres;		# install via package name, for the active PG version
pig install omni_json;		# install by extension name, for the current active PG version

pig install omni_json -v 18;   # install for PG 18
pig install omni_json -v 17;   # install for PG 17
pig install omni_json -v 16;   # install for PG 16
pig install omni_json -v 15;   # install for PG 15
pig install omni_json -v 14;   # install for PG 14

CREATE EXTENSION omni_json;

SELECT omni_json.define_table_mapping(example, '{}');

SELECT omni_json.define_table_mapping(example, '{
    "columns": { "dob": { "path": "date_of_birth" } }
}');

SELECT omni_json.define_table_mapping(example, '{
    "columns": {
        "first_name": { "path": ["name", "first"] },
        "last_name":  { "path": ["name", "last"] }
    }
}');

SELECT omni_json.define_table_mapping(example, '{
    "columns": { "dob": { "exclude": true } }
}');

{
    "columns": {
        "password": {
            "transform": {
                "input":  { "type": "text", "function": "encrypt_password" },
                "output": { "type": "text", "function": "mask_password" }
            }
        }
    }
}

SELECT to_jsonb(products.*) FROM products;

INSERT INTO people (dob, first_name, last_name)
    (SELECT dob, first_name, last_name
     FROM jsonb_populate_record(null::people,
         '{"first_name": "Jane", "last_name": "Doe", "dob": "1981-12-12"}'));