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!