JSON_VALUE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_VALUE
function is used to extract content from JSON documents using a JSON path expression.
SELECT json_value( '{"a":[1,2,3]}', '$.a[1]' ) FROM dual
create.select(jsonValue( val(JSON.valueOf("{\"a\":[1,2,3]}")), "$.a[1]" ) .fetch();
The result would look like this:
+------------+ | json_value | +------------+ | 2 | +------------+
If the value does not matter, but you just want to check for a value's existence, use the JSON_EXISTS predicate.
Dialect support
This example using jOOQ:
jsonValue(val(json("[1,2]")), "$[*]")
Translates to the following dialect specific expressions:
-- DB2, MARIADB, ORACLE json_value('[1,2]', '$[*]') -- MYSQL, SQLITE json_extract('[1,2]', '$[*]') -- POSTGRES, YUGABYTEDB jsonb_path_query_first( CAST('[1,2]' AS jsonb), cast('$[*]' as jsonpath) ) -- SQLSERVER ( SELECT c FROM openjson('[1,2]', '$') WITH (c varchar(max) '$[*]') ) -- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DERBY, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, -- INFORMIX, MEMSQL, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SYBASE, TERADATA, TRINO, 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!