Continuing theme started in https://twitter.com/asm0di0/status/929752657878798336 about JSONB support.
JSONB in postgres supports several operators, which can be found here <https://www.postgresql.org/docs/current/static/functions-json.html>. 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 <http://www.jsonschema2pojo.org/> 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! -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
