Available in versions: Dev (3.20) | Latest (3.19) | 3.18

Kotlin JSON access

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

JSON array elements or object attributes can be accessed using the JSON_GET_ELEMENT function or JSON_GET_ATTRIBUTE function which translate to the JSON subscript syntax, or something equivalent:

SELECT
  JSON_ARRAY(1, 2)->1
  JSON_OBJECT(KEY 'a' VALUE 1)->'a'
create.select(
  jsonGetElement(jsonArray(value(1), value(2)), 1)
  jsonGetAttribute(jsonObject("a", value(1)), "a")).fetch();

Using the kotlin extensions module, these operators are also made available on Field<JSON;> and Field<JSONB> directly:

package org.jooq.kotlin

operator fun Field<JSON?>.get(index: Int) = jsonGetElement(this, index)
operator fun Field<JSON?>.get(index: Field<Int?>) = jsonGetElement(this, index)
operator fun Field<JSON?>.get(name: String) = jsonGetAttribute(this, name)
operator fun Field<JSON?>.get(name: Field<String?>) = jsonGetAttribute(this, name)

// [... and more]

This allows for the leaner version below:

create.select(
  jsonArray(value(1), value(2))[1],
  jsonObject("a", value(1))["a"]).fetch();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo