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

JSON_KEYS

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

The JSON_KEYS function is a non-standard JSON function inspired by MySQL's JSON_KEYS function, which can be used to extract keys of a JSON object into an JSON array.

SELECT json_keys(json_object(
  KEY 'a' VALUE 1
  KEY 'b' VALUE 2
))
create.select(jsonKeys(jsonObject(
          key("a").value(1), 
          key("b").value(2))))
      .fetch();

The result would look like this:

+-----------+
| json_keys |
+-----------+
| ["a","b"] | 
+-----------+

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

-- 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)
)

-- COCKROACHDB
(
  SELECT coalesce(
    json_agg(j),
    json_build_array()
  )
  FROM json_object_keys(json_build_object(
    'a', CAST(1 AS int4),
    'b', CAST(2 AS int4)
  )) as j(j)
)

-- MARIADB
json_keys(json_merge_preserve(
  '{}',
  json_object('a', 1),
  json_object('b', 2)
))

-- MYSQL
json_keys(json_object(
  'a', 1,
  'b', 2
))

-- ORACLE
json_keys(json_object(
  KEY 'a' VALUE nvl(NULL, 1),
  KEY 'b' VALUE nvl(NULL, 2)
))

-- SNOWFLAKE
object_keys(object_construct_keep_null(
  'a', 1,
  'b', 2
))

-- SQLITE
(
  SELECT json_group_array("key")
  FROM json_each(json_object(
    'a', 1,
    'b', 2
  ))
)

-- TRINO
CAST(map_keys(cast(CAST(map_from_entries(ARRAY[
  row(
    'a',
    CAST(1 AS json)
  ),
  row(
    'b',
    CAST(2 AS json)
  )
]) AS json) as map(varchar, json))) AS json)

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DB2, DERBY, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, MEMSQL, 
-- REDSHIFT, SQLDATAWAREHOUSE, SQLSERVER, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

Feedback

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

The jOOQ Logo