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:
BigQuery, DB2, MariaDB, Oracle
json_value('[1,2]', '$[*]')
ClickHouse
JSON_VALUE('[1,2]', '$[*]')
DuckDB
json_extract_string('[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, CockroachDB, Databricks, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!