Is PgSync supports Postgres Json related functions

[korvad]

PGSync version: 2.5.0

Postgres version: 12.13-1.pgdg110+1

Elasticsearch version: 7.17.7

Redis version: 7.0.11

Python version: 3.7

Problem Description:

Hi @toluaina
First of all thx for great tool!

Could you clarify is PgSync supports Postgres json/jsonb related functions?
According to documentation https://pgsync.com/tutorial/json-fields/

Postgres functions from here
https://www.postgresql.org/docs/current/functions-json.html
are supported

Nevertheless when using schema.json that looks like

[
    {
        "database": "postgres",
        "index": "products",
        "nodes": {
            "table": "product",
            "schema": "main",
            "columns": [
                "id",
                "name",
                "description",
                "jsonb_path_query_array(source, '$.dataset[*].item.metadata.columns[*] ? (@.public == true).name')"
            ]
        }
    }
]

DDL

create table product
(
    id                 uuid                     not null primary key,
    name               text                     not null,
    description        text,    
    source             jsonb                    not null    
);

and json content in product.source property in Postgres looks like

{
  "dataset": [
    {
      "item": {
        "name": "Item1",
        "metadata": {
          "columns": [
            {
              "name": "Value1",
              "public": true,
              "mandatory": false,
              "description": "Description1"
            },
            {
              "name": "Value2",
              "public": false,
              "mandatory": false,
              "description": "Description2"
            }
          ]
        }
      }
    }
  ]
}

Error Message take place:

root@9d9dab9ab793:/usr/src/app# pgsync -c schema.json 
 0:00:00.399905 (0.40 sec)
Traceback (most recent call last):
  File "/usr/local/bin/pgsync", line 7, in <module>
    sync.main()
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.7/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/pgsync/sync.py", line 1450, in main
    sync: Sync = Sync(document, verbose=verbose, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/pgsync/singleton.py", line 18, in __call__
    *args, **kwargs
  File "/usr/local/lib/python3.7/site-packages/pgsync/sync.py", line 97, in __init__
    self.tree.build(self.nodes)
  File "/usr/local/lib/python3.7/site-packages/pgsync/node.py", line 313, in build
    node.add_child(self.build(child))
  File "/usr/local/lib/python3.7/site-packages/pgsync/node.py", line 303, in build
    base_tables=data.get("base_tables", []),
  File "<string>", line 13, in __init__
  File "/usr/local/lib/python3.7/site-packages/pgsync/node.py", line 135, in __post_init__
    self.setup()
  File "/usr/local/lib/python3.7/site-packages/pgsync/node.py", line 199, in setup
    f'Column "{column_name}" not present on '
pgsync.exc.ColumnNotFoundError: 'Column "jsonb_path_query_array(source, \'$.dataset[*].item.metadata.columns[*] ? (@.public == true).name\')" not present on table "product"'

[toluaina]

Hi,

• PGSync supports a subset of JSONB functions
• You can specify and extract columns.
• Here is an example

You specify columns names as shown below

[
    {
        "database": "postgres",
        "index": "products",
        "nodes": {
            "table": "product",
            "schema": "main",
            "columns": [
                "id",
                "name",
                "description",
                "source>some_field"
            ]
        }
    }
]

[korvad]

ok, thx for answer