Filter for parsing of JSON fields

[nickmyatt]

JsonReceiver implements a basic mapping between Postgres scalar types and similar JSON scalar types:

• string
• boolean
• number

it does not implement any mapping to compound (object, array) types however, even when an "obvious" mapping exists (for example from Postgres json[b] values); all other values are emitted as JSON strings (presumably according to Postgres default text representation for that type?), meaning that we see output like:

{
  "tx": [
    {
      "schema": "public",
      "table": "questions",
      "colnames": [
        "title",
        "tags",
      ],
      "coltypes": [
        "text",
        "question_tags",
      ],
      "values": [
        "How do I parse this nested JSON?",
        "[\"json\", \"postgresql\", \"python\"]",
      ],
      "op": "I"
    }
  ]
}

when it might be more convenient for the replisome consumer to get something like

{
...
      "values": [
        "How do I parse this nested JSON?",
        ["json", "postgresql", "python"],
...
}

note also that the coltype of tags is question_tags and not jsonb, even though we have defined:

CREATE DOMAIN question_tags AS jsonb;

There are at least 2 good reasons why the JsonReceiver should not (by default) emit nested JSON in this way, however:

1. the empty object {} is currently used to represent the special case of "unchanged TOASTed value" in the values array, and since large json[b] fields are TOASTable, there would be no way to distinguish the "unchanged" and "changed to empty object" cases (at least without some metadata outside the values array, which the consumer must know to look for).

2. if the consumer is just inserting values into another Postgres instance, it would need to serialise the nested JSON again (an unnecessary decode/encode roundtrip)

It is proposed to instead provide a filter class which can be optionally plugged in to parse nested JSON before it reaches the consumer class. The implementation would be trivial for json[b] fields (just call json.loads() on the relevant fields in values).

There is a problem with DOMAIN type aliases, however; as mentioned above JsonReceiver emits the alias and not the underlying type, so there is no way for the proposed filter to detect these fields as JSON-able automatically. The filter would need to be configured with a set of type aliases which it should also parse.

Finally, we could extend the filter to also convert other JSON-like Postgres types (hstore, text[] come to mind), perhaps by using the decoding layer from psycopg2?

[nickmyatt]

New proposal:

A PythonTypes filter, with an API as illustrated in this example:

   filters:
      - class: PythonTypes
        options:
            types:
              - date
              - timestamptz
              - tags: json
              - whatever: path.to.my.converter

that is, types is a list where each element is either:

• Postgres builtin type (e.g. date, timestamptz)

  • declares that any instances of this type are to be decoded into instances of a similar Python type, according to a default decoder function (can mostly follow psycopg2 conventions for defaults)

• a mapping from type to Postgres builtin type (e.g. tags: json)

  • declares that the type tags (which may be a user-defined type) is to be treated in the same way as the builtin type json, i.e. passed through the same default decoder function

• a mapping from type to converter function path (e.g. whatever: path.to.my.converter)

  • declares that whatever (which may be user-defined type) is to be decoded using the function path.to.my.converter.

[dvarrazzo]

Observation: the marker {} for "unchanged TOASTed data" would be no more special: there could be several conversion functions whose python interpretation is an empty dictionary (trivially, json). As such, the marker is no more reliable downstream of the PythonTypes filter: we need another marker.

Two ways come to mind:

• create a singleton UNCHANGED_TOAST object and return it. The consumers must be aware of it though, and handle it appropriately
• attach keynames back to all the change object returned by the filter. If the record contains any unchanged TOAST drop the entry from values as well as from keynames so that the property that the two arrays are "zippable" remains.

I think the second method would make consumers easier to write. Note that it is still possible to detect changes in the schema, because on a schema change the keytypes array would be present too: this may probably need to be formalised better.