get array of all jsonb field keys in JOOQ - jsonb_object_keys

I have a jsonb stored in table:

{
"a": "x",
"b": "x",
"c": "x"
...
}

What I need is to return an array of all keys from this jsonb like this: ["a", "b", "c"]

I'm using old version of Jooq there is no json_object_keys method, so is there any way to extract these keys in Joq?

Solution

jOOQ 3.18 will have support for the MySQL style JSON_KEYS() function, see #14046. As you can see in that manual page, this jOOQ API call:

jsonKeys(jsonObject(key("a").value(1), key("b").value(2)))

Will translate to something like this in CockroachDB, PostgreSQL, YugabyteDB:

-- AURORA_POSTGRES, POSTGRES, YUGABYTEDB
(
  SELECT coalesce(
    json_agg(j),
    json_build_array()
  )
  FROM json_object_keys(json_build_object(
    'a', CAST(1 AS int),
    'b', CAST(2 AS int)
  )) as j(j)
)

Alternatively, as per @a_horse_with_no_name's comment, an improved emulation might be implemented via jsonb_path_query_array in PostgreSQL 12+

You can obviously do this already today using plain SQL templating, until the above is released.