JSON_EXTRACT is a function in SQL that allows you to extract a value from a JSON string. It is often used with the SELECT
statement to retrieve specific values from a table.
๐ Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
Simple JSON_EXTRACT example
Here is an example of how to use the JSON_EXTRACT function in a SELECT
statement:
SELECT JSON_EXTRACT(json_column, '$.key1.key2')
FROM mytable
WHERE json_column IS NOT NULL;
In this example, the JSON_EXTRACT function is used to extract the value associated with the keys key1
and key2
from the json_column
in the mytable
table.
The function takes two arguments: the JSON string and the path to the value you want to extract. The path is specified using the $
sign to indicate the root of the JSON object, followed by a series of keys separated by dots (.
).
If the JSON string in the json_column
is structured like this:
{
"key1": {
"key2": "value"
}
}
Then the JSON_EXTRACT function would return the string "value"
.
Using JSON_EXTRACT on nested Data
You can also use the JSON_EXTRACT function to extract values from nested JSON objects. For example, if the JSON string in the json_column
is structured like this:
{
"key1": {
"key2": {
"key3": "value"
}
}
}
Then you can use the JSON_EXTRACT function with a path like this: $.key1.key2.key3
to extract the value "value"
.
Using JSON_EXTRACT on arrays
Another useful feature of the JSON_EXTRACT function is that you can use it to extract values from JSON arrays. For example, if the JSON string in the json_column
is structured like this:
{
"key1": [
{
"key2": "value1"
},
{
"key2": "value2"
}
]
}
You can use the JSON_EXTRACT function with a path like this: $.key1[*].key2
to extract an array of values: ["value1", "value2"]
. The [*]
syntax indicates that you want to extract all values in the array, rather than just a specific index.
Using JSON_EXTRACT in other statements
In addition to the SELECT
statement, you can also use the JSON_EXTRACT function in other SQL statements, such as INSERT
, UPDATE
, and DELETE
, to manipulate JSON data in your database.
JSON_EXTRACT summary
Overall, the JSON_EXTRACT function is a powerful tool for working with JSON data in SQL. It allows you to easily extract specific values from JSON strings, even if the JSON is nested or contains arrays.