Json fields

PGSync supports Postgres JSON and JSONB operators. This allows you to extract data within JSON fields.

These operators are defined here

For example. Given a Postgres JSON field called doc

{
    "a": {
        "b": {
            "c": [0, 1, 2, 3, 4]
        }
    },
    "x": [
        {
            "y": 0,
            "z": 5
        },
        {
            "y": 1,
            "z": 6
        }
    ]
}

We can define this JSON schema to extract the fourth element of the {a, b, c} path, we can define this schema

[
    {
        "database": "book",
        "index": "book",
        "nodes": [
            {
                "table": "book",
                "schema": "my_book_library",
                "columns": [
                    "doc#>{a,b,c}->4"
                ]
            }
        ]
    }
]

We can define this JSON schema, to get the first array element of field x.

[
    {
        "database": "book",
        "index": "book",
        "nodes": [
            {
                "table": "book",
                "schema": "my_book_library",
                "columns": [
                    "doc->x->0"
                ]
            }
        ]
    }
]

Info

doc is the field name in the database