Support for JSONb in Postgres

[Pavel Finkelshtein]

Continuing theme started in https://twitter.com/asm0di0/status/929752657878798336 about JSONB support.

JSONB in postgres supports several operators, which can be found here. 

Couple example of how we work with it in in JOOQ:

return ctx
            .select(
                sum(field("(short_deal_json -> 'contractInfo' ->> 'price')::NUMERIC", BigDecimal::class.java))
                    - sum(field("(short_deal_json ->> 'deposit')::NUMERIC", BigDecimal::class.java))
            )
            .from(DEAL)
            .where(DEAL.STATE.eq(DealState.REGISTRATION))
            .fetchOneInto(BigDecimal::class.java)

And even

ctx
        .select(DEAL.DEAL_NO)
        .from(DEAL)
        .where(normalizedField("buyer,person,firstName").eq(firstName))
        .and(normalizedField("buyer,person,lastName").eq(lastName))
        .and(
            if (patronymic == null) {
                DSL.field("request_json #>> '{buyer,person,patronymic}'").isNull
            } else {
                normalizedField("buyer,person,patronymic").eq(patronymic)
            }
        )
        .and(DSL.field("request_json #>> '{contractInfo,cadastralNum}'", String::class.java).eq(cadastralNum))
        .and(
            if (contractNumber == null) {
                DSL.field("request_json #>> '{contractInfo,contractNumber}'").isNull
            } else {
                normalizedContractField("contractInfo,contractNumber").eq(contractNumber)
            }
        )
        .fetch(DEAL.DEAL_NO)

I think it would make sense to implement some type of sub-dsl for work with JSON values, like GSON's one: asJsonObjetc, asJsonArray and so on.

Regarding 3rd point of my tweet, JSON introspection. I think that it may be possible to convert JSON to Records, by using this project. Of course, generated source will need some corrections, according to Record semantics, but it will give developers ability to work with json fields almost like with related tables. Moreover, it will be possible to make updates to sub-entities and then update whole entity without performing ultra-complex queries!

[Lukas Eder]

Hi Pavel,

Thank you very much for your message, and for following up after your tweets.

Indeed, PostgreSQL has quite a few interesting JSON(B) operators as well as many other operators for its vendor-specific data types.

One of the problems with these things is that they are very poorly standardised, and it will thus be rather difficult for jOOQ to standardise on such an API. The other thing is the unfortunate choice of ascii characters in PostgreSQL, which cannot be mapped easily to Java methods (in Scala, it would be possible). Yes, there's always an ordinary function name backing the implementation of such operators, but they're not really idiomatic. This is just to give you a hint why we haven't done much in this area yet.

Another problem with JSON is the fact that Java still doesn't have a standard JSON binding API, akin to JAXB for XML. We could pick an arbitrary 3rd party dependency (we won't) or roll our own (we shouldn't). So, it's not easy to move forward, here.

More comments inline:

2017-11-14 14:38 GMT+01:00 Pavel Finkelshtein <pavel.fin...@gmail.com>:

I think it would make sense to implement some type of sub-dsl for work with JSON values, like GSON's one: asJsonObjetc, asJsonArray and so on.

Absolutely, such a sub-dsl would be quite nice. We already have a few methods in PostgresDSL, mainly for array support. But again, JSON is more difficult to standardise.

Nothing will keep you from publishing your own third-party mini-dsl on GitHub, though! :) We'll certainly help promote it.

 

Regarding 3rd point of my tweet, JSON introspection. I think that it may be possible to convert JSON to Records, by using this project. Of course, generated source will need some corrections, according to Record semantics, but it will give developers ability to work with json fields almost like with related tables. Moreover, it will be possible to make updates to sub-entities and then update whole entity without performing ultra-complex queries!

I'm aware of that tool. It's definitely out of scope for jOOQ to integrate with such third party tools (and maintain the integration). But again, nothing keeps you from rolling your own!

This is not to say that such an integration wouldn't make sense in jOOQ. But given the cost / benefit ratio (including the ratio of maintaining such a solution in the long run), I just prefer to invest a bit more in other features first...

Thanks,

Lukas

[Pavel Finkelshtein]

Hi Lukas,

One of the problems with these things is that they are very poorly standardised, and it will thus be rather difficult for jOOQ to standardise on such an API

If I understand problem correctly, JSONB is standartized not by SQL standart, but by Postgres itself. So it should be possible to create  custom type (PostgresJson for example), which will have it's own custom functions.

there's always an ordinary function name backing the implementation of such operators, but they're not really idiomatic

There is pretty idiomatic way of work with JSON, which is described in Java API for JSON Processing (http://www.oracle.com/technetwork/articles/java/json-1973242.html). And it should be absolutely possible to migrate such API to Java API for JSON Processing when it'll become official.

It's definitely out of scope for jOOQ to integrate with such third party tools (and maintain the integration). 

It was quite predictable. I'm fully agree, that this is not high-priority feature, remembering that Postgres customers are not paid ones. It was just idea 

[Lukas Eder]

2017-11-16 9:38 GMT+01:00 Pavel Finkelshtein <pavel.fin...@gmail.com>:

Hi Lukas,

One of the problems with these things is that they are very poorly standardised, and it will thus be rather difficult for jOOQ to standardise on such an API

If I understand problem correctly, JSONB is standartized not by SQL standart, but by Postgres itself. So it should be possible to create  custom type (PostgresJson for example), which will have it's own custom functions.

Yes, that would absolutely be possible.

Having said so, if this is about a data type binding, it would be really super simple to introduce your own binding (or write a library that you share on GitHub), which can bind any third party JSON library to JSON(B) in PostgreSQL:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

 

It was quite predictable. I'm fully agree, that this is not high-priority feature, remembering that Postgres customers are not paid ones. It was just idea 

Oh, we have quite a few paying customers that use jOOQ with PostgreSQL. But just because they're paying doesn't mean we add every feature that has ever been requested if it doesn't fit the roadmap and the priorities of all of our customers / users :)

Again, the main reason why we haven't done this yet is not that PostgreSQL is available from the jOOQ OSS Edition, but because it is really difficult to make the right decision that works for everyone, when it comes to binding JSON. We don't want a PostgreSQL specific solution here. Oracle, SQL Server, DB2, MySQL also support JSON, and the solution would have to work for everyone.

Thanks,

Lukas

[Паша]

Thank you very much, Lukas. Point is perfectly clear!

ср, 22 нояб. 2017 г. в 14:38, Lukas Eder <lukas...@gmail.com>:

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/BCcli15pbm0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.