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:
ClickHouse
JSON_VALUE('[1,2]', '$[*]')
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) '$[*]')
)
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!