Support Postgres JSON to arrow2

[glennpierce]

I wanted support to get JSON columns from a postgresql db as below.

I implemented this at
git@github.com:glennpierce/connector-x.git
branch postgres_json

I haven't request a PR as I only implemented for arrow2 and I not sure you would require arrow as well.
Also I store the JSON as a string in arrow2. I'm am not familiar with aroow enough to know that this was the right approach.
What do you think ?

let sql = "SELECT
                hs.haystack_tags->'carnego_ref' as site_ref,
                hsg.haystack_tags->'carnego_ref' as site_group_ref,
                hsg.namespace AS site_group_namespace,
                hs.namespace AS site_namespace,
                COALESCE(hs.smart_htc_config, hsg.smart_htc_config) as smart_htc_config,
                COALESCE(hs.portfolio_config, hsg.portfolio_config) as portfolio_config,
                COALESCE(hs.performance_config, hsg.performance_config) as performance_config
                FROM haystack_site hs
                LEFT JOIN haystack_site_group hsg ON hs.haystack_tags->'carnego_site_group_ref' = hsg.haystack_tags->'carnego_ref'
                WHERE hs.haystack_tags->'carnego_namespace' IS NOT NULL AND
                    hsg.haystack_tags->'carnego_namespace' IS NOT NULL AND
                    (COALESCE(hs.smart_htc_config, hsg.smart_htc_config) IS NOT NULL OR
                     COALESCE(hs.portfolio_config, hsg.portfolio_config) IS NOT NULL OR
                     COALESCE(hs.performance_config, hsg.performance_config) IS NOT NULL) ORDER BY 2,1".to_string();

[wangxiaoying]

Hi @glennpierce , thanks for the PR! Actually JSON could be directly supported by adding a transport rule:

{ JSON[Value]                 => LargeUtf8[String]           | conversion option }

// {postgres's logical type[postgres's physical type]   => arrow2's logical type[arrow2's physical type] | indicates the conversion function from Value to String need to be defined manually}

and the corresponding type conversion function that converts Value to String:

impl<P, C> TypeConversion<Value, String> for PostgresArrow2Transport<P, C> {
    fn convert(val: Value) -> String {
        val.to_string()
    }
}

in connectorx/src/transports/postgres_arrow2.rs for arrow2.

This will convert postgres's json type to arrow's string type before writing to arrow result, so ArrowAssoc won't need to deal with json.

For arrow, the support could be similar done in connectorx/src/transports/postgres_arrow.rs. It is not required but feel free to also support it.

[glennpierce]

Hi @glennpierce , thanks for the PR! Actually JSON could be directly supported by adding a transport rule:

{ JSON[Value]                 => LargeUtf8[String]           | conversion option }

// {postgres's logical type[postgres's physical type]   => arrow2's logical type[arrow2's physical type] | indicates the conversion function from Value to String need to be defined manually}

and the corresponding type conversion function that converts Value to String:

impl<P, C> TypeConversion<Value, String> for PostgresArrow2Transport<P, C> {
    fn convert(val: Value) -> String {
        val.to_string()
    }
}

in connectorx/src/transports/postgres_arrow2.rs for arrow2.

This will convert postgres's json type to arrow's string type before writing to arrow result, so ArrowAssoc won't need to deal with json.

For arrow, the support could be similar done in connectorx/src/transports/postgres_arrow.rs. It is not required but feel free to also support it.

When I comment the ArrowAssoc impl I added I get

the trait bound serde_json::Value: arrow2::arrow_assoc::ArrowAssoc is not satisfied

[wangxiaoying]

Redirect to #234