# Extracting and Converting JSON Values

## Extracting JSON Values

`subvalue_mapping` specifies which subvalues are extracted and the column or variable to which each one is assigned.

`LOAD DATA` uses the `::`-separated list of keys in a `subvalue_path` to perform successive key lookups in nested JSON objects, as if applying the [`::` SQL operator](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-keypaths.md). Unlike the `::` operator, `subvalue_path` may not be used to extract an element of a JSON array. The path `%` refers to the entire JSON value being processed. Leading `%::` may be omitted from paths which are otherwise non-empty.

If a path can’t be found in an input JSON value, then if the containing element of `subvalue_mapping` has a `DEFAULT` clause, its `literal_expr` will be assigned; otherwise, `LOAD DATA` will terminate with an error.

Path components containing whitespace or punctuation must be surrounded by backticks. For example, the paths ``%::`a.a`::b`` and `` `a.a`::b `` will both extract `1` from the input object `{"a.a":{"b":1},"c":2}`.

Array elements may be indirectly extracted by applying [`JSON_EXTRACT_<type>`](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-extract-type.md) in a `SET` clause.

## Converting JSON Values

Before assignment or set clause evaluation, the JSON value extracted according to a `subvalue_path` is converted to a binary collation SQL string whose value depends on the extracted JSON type as follows:

| JSON Type      | Converted Value                                                                                                                                         |
| -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `null`         | SQL`NULL`                                                                                                                                               |
| `true`/`false` | `"1"`/`"0"`                                                                                                                                             |
| `number`       | Verbatim, from extracted string.                                                                                                                        |
| `string`       | All JSON string escape sequences, including[escape sequences](https://tools.ietf.org/html/rfc8259#section-7)are converted to UTF-8. Verbatim otherwise. |
| `array`        | Verbatim, from extracted string. For example,`'[1,2]'`                                                                                                  |
| `object`       | Verbatim, from extracted string. For example,`'{"k":true}'`                                                                                             |

Conversion is not recursive. So, for example, `true` is not converted to `"1"` when it is a subvalue of an object which is being extracted whole.

***

Modified at: April 3, 2025

Source: [/db/v9.1/load-data/load-data-from-files/load-data-from-json-files/extracting-and-converting-json-values/](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-json-files/extracting-and-converting-json-values/)

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