# **JSON_EXTRACT_STRING()**

## **Function Description**

`JSON_EXTRACT_STRING()` is used to extract the string value of the specified path from JSON data.

If you compare in where conditions:

- If type is json object comparison, use [JSON EXTRACT()](json_extract.md);
- If the type is a string type, use JSON_EXTRACT_STRING();
- If type is float or int, use [`JSON_EXTRACT_FLOAT64()`](json_extract_float64.md).

## **Grammar Structure**

```sql
select col_name from tab_name where json_extract_string(jsonDoc, pathExpression)= number;
```

## **Parameter definition**

| Parameters | Description |
| ---- | ---- |
| jsonDoc | This is a column or expression containing JSON data. |
| pathExpression | Indicates the path to access a value in a JSON document. Only one path can be received at a time. The path starts with `$`, representing the root of the JSON document, followed by the dot number `.` and key name or access elements of the array with square brackets [ ]. |
| number | Specifies the value to be extracted in JSON data, which is a numerical type. |

The path expression must start with the `$` character:

- `,` followed by a key name, name the members in the object with the given key. Key names need to be included in double quotes.

- `[N]`: After selecting *path* of the array, name the value at position `N` in the array. The array position is an integer starting from zero. If the array is negative, an error will be reported.

- The path can contain `*` or `**` wildcard:

   + `.[*]` calculates the values of all members in the JSON object.

   + `[*]` calculates the values of all elements in the JSON array.

   + `prefix**suffix`: Calculates all paths that start with a named prefix and end with a named suffix.

- Paths (or data that do not exist) in the document are evaluated as `NULL`.

## **Example**

```text
-- Create a test table
CREATE TABLE student(
    id INT,
    info JSON
);

-- Insert test data
INSERT INTO student VALUES
    (1, '{"name": "tom", "age": 18, "scores": [85, 90, 78]}'),
    (2, '{"name": "bob", "age": 20, "scores": [75, 82, 91]}');

-- Extract string value
mysql> SELECT id FROM student WHERE json_extract_string(info, '$.name') = 'tom';
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)

-- The non-existent path returns NULL
mysql> SELECT json_extract_string(info, '$.address') FROM student;
+---------------------------------------------+
| json_extract_string(info, $.address) |
+---------------------------------------------+
| NULL |
| NULL |
+---------------------------------------------+
2 rows in set (0.00 sec)
```