SQL JSON_EXTRACT Walkthrough With Examples

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.

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.