Table Mapping

This extension allows you to map tables to JSON, extending to_jsonb function by overloading it for a particular table type.

By default, one can use to_jsonb on any existing table type and get a JSON out:

 create table example (
                          id           integer primary key generated always as identity,
                          first_name   text,
                          last_name    text,
                          dob          date
 );

select to_jsonb(example.*) from example;

results in

{
  "id": 1, 
  "dob": "1971-12-12", 
  "last_name": "Doe", 
  "first_name": "John"
}

However, the moment you need to perform some transformation, it becomes less useful. This is where this extension helps. One can define a mapping for a table using omni_json.define_table_mapping(type, annotation) where annotation is a JSON document:

select omni_json.define_table_mapping(example, '{}')

Configuring columns

By specifying columns object with a for a given column, one can configure column properties.

select omni_json.define_table_mapping(example, $$
{
  "columns": { "my_column": { ... } }
}
$$);

Renaming keys

path annotation for a column can be used to rename it

{
  "columns": {
    "dob": {
      "path": "date_of_birth"
    }
  }
}

Now, if you re-run to_jsonb you will get this:

{
  "id": 1, 
  "date_of_birth": "1971-12-12", 
  "last_name": "Doe", 
  "first_name": "John"
}

Moving keys

path annotation can also be used to move columns to a given path when given an array of keys:

{
  "columns": {
    "first_name": {
      "path": ["name", "first"]
    },
    "last_name": {
      "path": ["name", "last"]
    }
  }
}

Now, if you re-run to_jsonb you will get this:

{
  "id": 1, 
  "date_of_birth": "1971-12-12",
  "name": {
    "last": "Doe",
    "first": "John"
  }
}

In fact, it can also move columns to arrays.

{
  "columns": {
    "first_name": {
      "path": ["name", 0]
    },
    "last_name": {
      "path": ["name", 1]
    }
  }
}

Now, if you re-run to_jsonb you will get this:

{
  "id": 1, 
  "date_of_birth": "1971-12-12",
  "name": [
    "John",
    "Doe"
  ]
}

Column exclusion

Imagine we don't want to show date of birth in the above example. We can do so by simply excluding it:

{
  "columns": {
    "dob": {
      "exclude": true
    }
  }
}

Now, if you re-run to_jsonb you will get this:

{
  "id": 1,
  "last_name": "Doe",
  "first_name": "John"
}

Column transformation

In certain cases (such as dealing with sensitive information or non-conforming data), it would be beneficial to be able to transform data when it is converted either from JSON or to JSON.

This is where transform option comes into the picture.

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

The above will apply encrypt_password(text) to convert password in the JSON object to the value in a record. Other types are: json and jsonb and they will call encrypt_password(json) and encrypt_password(jsonb) respectively.

Similarly, there's support for serializing back to JSON:

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

This will make to_jsonb call mask_password(password) and expect it to return text. Similarly, other supported types for the return value are json and jsonb.

Operational Guide

Retrieving JSON

You can use to_jsonb(table_name.*) as you would typically do, but the transformation rules described above will apply.

select
    to_jsonb(products.*)
from
    products

Updating from JSON

You can update explicitly listed fields using the following construct:

update people
set
    -- `dob`, `first_name` and `last_name` are allowed to be updated
    (dob, first_name, last_name) =
        (select
             dob,
             first_name,
             last_name
         from
             jsonb_populate_record(people.*,
                                   '{"dob": "1981-12-12"}'))
where
    id = some_id 

Inserting JSON

Similarly to update, JSON can be also inserted

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"}'))