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.

Need to convert JSON data to SQL INSERT statements? Try our free JSON to SQL Converter tool.