# JSON Keypaths

Some built-in JSON functions operate on sub-elements in their first argument. A particular sub-element is selected by means of a keypath, which consists of a list of string keys and/or integer array indexes. How a particular key is treated depends on the structure of the JSON value.

The following table and values were used in the examples:

```sql
CREATE TABLE greek_alpha (ltr_name text(25));

DESC greek_alpha;


```

```output

+----------+------+-----+-----+---------+-------+
| Field    | Type | Null| Key | Default | Extra |
+----------+------+-----+-----+---------+-------+
| ltr_name | text | YES |     | NULL    |       |
+----------+------+-----+-----+---------+-------+

```

```sql
INSERT INTO greek_alpha (ltr_name) VALUES ('{
    "posIds": [
        { "text": "alpha" }, 
        { "text": "beta" },
        { "text": "gamma" },
        { "text": "delta" }  
    ]
}');

```

In the following examples, both of the `SELECT` queries will have the same result, "gamma". JSON arrays begin with zero so in these examples "gamma" is in the second position of the array.

```sql
SELECT JSON_EXTRACT_JSON(ltr_name, 'posIds', 2, 'text') FROM greek_alpha;


```

```output

+---------------------------------------------------+
| JSON_EXTRACT_JSON(ltr_name, 'posIds', 2, 'text')  |
+---------------------------------------------------+
| "gamma"                                           |
+---------------------------------------------------+

SELECT ltr_name::posIds::`2`::text FROM greek_alpha;


+------------------------------+
| ltr_name::posIds::`2`::text  |
+------------------------------+
| "gamma"                      |
+------------------------------+
```

In the `UPDATE` query, the built-in function `JSON_DELETE_KEY` takes a keypath. The function will generate a new JSON object with the second element being removed.

```sql
UPDATE greek_alpha SET ltr_name = JSON_DELETE_KEY(ltr_name, 'posIds', 2);

SELECT * FROM greek_alpha;


```

```output

+----------------------------------------------------------------+
| ltr_name                                                       |
+----------------------------------------------------------------+
| {"posIds":[{"text":"alpha"},{"text":"beta"},{"text":"delta"}]} |                    |
+----------------------------------------------------------------+
```

> **📝 Note**: For more info on JSON operators `::`, `::$`, and `::%`, see [Accessing Fields in a JSON Object](https://docs.singlestore.com/db/v9.1/create-a-database/using-json.md).

The following JSON functions accept keypaths:

* [JSON\_DELETE\_KEY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-delete-key.md)
* [JSON\_EXTRACT\_\<type>](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-extract-type.md)
* [JSON\_SET\_\<type>](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-set-type.md)

***

Modified at: April 3, 2023

Source: [/db/v9.1/reference/sql-reference/json-functions/json-keypaths/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-keypaths/)

(An index of the documentation is available at /llms.txt)