# Load JSON Files Examples

This topic demonstrates common methods for loading JSON data into SingleStore using the [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md) command.

The examples show how to:

* Map JSON fields to multiple table columns.
* Load entire JSON values into a single JSON column.
* Use `DEFAULT`, `SET`, and `WHERE` clauses when loading JSON data.
* Load a JSON file that contains a top-level array of objects using a pipeline.
* Extract nested JSON fields using the `:: subvalue_path` syntax in a pipeline.

Refer to [Load JSON Files with LOAD DATA](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-json-files/load-json-files-with-load-data.md) for more information on syntax and usage.

## Map JSON Fields to Multiple Columns

This example extracts values from JSON fields and loads them into individual table columns.

This example uses `books.json` file which contains the following JSON data:

```json
{
  "title": "Onyx Storm",
  "author": "Rebecca Yarros",
  "details": {
    "publisher": "Entangled:Red Tower Books",
    "numpages": 544,
    "publication date": "January 21, 2025",
    "printings": [2,1.3],
    "series": "The Empyrean"
  }
}
{
  "title": "The Maid",
  "author": "Nita Prose",
  "details": {
    "publisher": "Ballantine Books",
    "numpages": 385,
    "publication date": "January 4, 2022",
    "printings": [0.5,0.75,1.2]
  }
}
{
  "title": "The Last Letter",
  "author": "Rebecca Yarros",
  "details": {
    "publisher": "Entangled:Amara",
    "numpages": 432,
    "publication date": "February 26, 2019",
    "printings": [0.25,0.5,0.5]
  }
}
```

Create the table named `books_json_multi_col`.

```sql
CREATE TABLE books_json_multi_col
(
    title_s2 TEXT,
    author_s2 TEXT,
    publisher_s2 TEXT,
    numpages_s2 INT,
    publication_date_s2 DATE,
    printings_s2 TEXT,
    series_s2 TEXT
);
```

Load the JSON Data into the table `books_json_multi_col`.

```sql
LOAD DATA S3 's3://singlestore-docs-example-datasets/json/books.json'
INTO TABLE books_json_multi_col
(
    title_s2 <- title,
    author_s2 <- author,
    publisher_s2 <- details::publisher,
    numpages_s2 <- details::numpages,
    @date_variable <- details::`publication date`,
    printings_s2 <- details::printings,
    series_s2 <- details::series DEFAULT NULL
)
FORMAT JSON
SET publication_date_s2 = TO_DATE(@date_variable, 'MONTH DD, YYYY');
```

In this example:

* Backticks (`` ` ``) are used around the JSON field name with a space (`` `publication date` ``).
* A variable (`@date_variable`) and a `SET` statement are used to convert the date string in the JSON file to a SingleStore `DATE` type.
* A default value is provided for the `series_s2` column as not all JSON values have a series field.

Verify the results:

```sql
SELECT * FROM books_json_multi_col;

```

```output

+-----------------+----------------+---------------------------+-------------+---------------------+----------------+--------------+
| title_s2        | author_s2      | publisher_s2              | numpages_s2 | publication_date_s2 | printings_s2   | series_s2    |
+-----------------+----------------+---------------------------+-------------+---------------------+----------------+--------------+
| Onyx Storm      | Rebecca Yarros | Entangled:Red Tower Books | 544         | 2025-01-21          | [2,1.3]        | The Empyrean |
| The Maid        | Nita Prose     | Ballantine Books          | 385         | 2022-01-04          | [0.5,0.75,1.2] | NULL         |
| The Last Letter | Rebecca Yarros | Entangled:Amara           | 432         | 2019-02-26          | [0.25,0.5,0.5] | NULL         |
+-----------------+----------------+---------------------------+-------------+---------------------+----------------+--------------+
```

## Load Entire JSON Values into a Single Column

This example uses `books.json` and loads each JSON value into a single column of type JSON. Refer to [Map JSON Fields to Multiple Columns](https://docs.singlestore.com/#section-id235621890940097.md) to view the data structure of `books.json` file.

Create the table named `books_json_single_col`.

```sql
CREATE TABLE books_json_single_col
(
    json JSON
);
```

Load the JSON Data in the table `books_json_single_col`.

```sql
LOAD DATA S3 's3://singlestore-docs-example-datasets/json/books.json'
INTO TABLE books_json_single_col
(
    json <- %
)
FORMAT JSON;
```

The `%` subvalue mapping loads the complete JSON value into the target column.

Verify the results:

```sql
SELECT JSON_PRETTY(json) FROM books_json_single_col;
```

> **📝 Note**: `JSON_PRETTY()` formats JSON values for readability. The function returns a string representation of the JSON document.

## Use `DEFAULT`, `SET`, and `WHERE` Clauses

This example uses `DEFAULT`, `SET`, and `WHERE` clauses to assign fallback values, transform extracted data, and filter rows when loading JSON data.

This example uses `json_mixed.json` file which contains the following JSON data:

```sql
{"b":true, "s":"A\u00AE\u0022A", "n":-1.4820790816978637e-25, "a":[1,2], "o":{"subobject":1}}
{"b":false}
"hello"
```

Create the table named `t`.

```sql
CREATE TABLE t(
    b BOOL NOT NULL,
    s TEXT,
    n DOUBLE,
    a INT,
    o JSON NOT NULL,
    whole LONGBLOB
);
```

Load the JSON data into the table `t`.

```sql
LOAD DATA LOCAL INFILE 'json_mixed.json'
INTO TABLE t
FORMAT JSON
(
    b <- b DEFAULT true,
    s <- s DEFAULT NULL,
    n <- n DEFAULT NULL,
    @avar <- a DEFAULT NULL,
    o <- o DEFAULT '{"subobject":"replaced"}',
    whole <- %
)
SET a = JSON_EXTRACT_DOUBLE(@avar, 1)
WHERE b = true;
```

Verify the results:

```sql
SELECT * FROM t;

```

```output


+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
| b | s     | n                       | a    | o                        | whole                                                                                         |
+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
| 1 | A®"A  | -1.4820790816978637e-25 |    2 | {"subobject":1}          | {"b":true, "s":"A\u00AE\u0022A", "n":-1.4820790816978637e-25, "a":[1,2], "o":{"subobject":1}} |
| 1 | NULL  |                    NULL | NULL | {"subobject":"replaced"} | hello                                                                                         |
+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
```

In this example, there are several JSON loading behaviors:

* SingleStore converts `true` to `"1"` for column `b`, but preserves the original JSON value in column `whole`. It then converts `"1"` to the Boolean value `1` for column `b`.
* SingleStore converts the escape sequences `\u00AE` and `\u0022` to UTF-8 characters for column `s`, but preserves the original JSON value in column `whole`. Converting `\u0022` in the column `whole` would make the JSON document invalid.
* The `WHERE` clause discards the second row because it does not satisfy the specified condition.
* SingleStore assigns `DEFAULT` values, such as `'{"subobject":"replaced"}'`, when it cannot find the paths specified in `subvalue_mapping` in the third row.
* The example assigns `a` to an intermediate variable so that the `SET` clause can extract an element from the array.
* The JSON file `json_mixed.json` contains top-level JSON values that are not JSON objects. For example, `"hello"` is a valid top-level JSON value.

## Load a JSON File with a Top-Level Array

This example loads the following JSON file that contains a top-level array of objects using a pipeline.

This example uses `jsonarrays.json` file which contains the following JSON data:

```json
[
  {
    "title": "Onyx Storm",
    "author": "Rebecca Yarros",
    "numpages": 544
  },
  {
    "title": "The Maid",
    "author": "Nita Prose",
    "numpages": 385
  },
  {
    "title": "The Last Letter",
    "author": "Rebecca Yarros",
    "numpages": 432
  }
]
```

Create a table named `books_s2`.

```sql
CREATE TABLE books_s2 (
    title_s2 TEXT,
    author_s2 TEXT,
    numpages_s2 INT
);
```

Create a pipeline named `books_pipe`.

```sql
CREATE PIPELINE books_pipe AS
LOAD DATA S3 's3://singlestore-docs-example-datasets/json/jsonarrays.json'
INTO TABLE books_s2 (
    title_s2 <- title,
    author_s2 <- author,
    numpages_s2 <- numpages
)
FORMAT JSON;
```

Start the pipeline.

```sql
START PIPELINE books_pipe FOREGROUND;
```

Verify the results:

```sql
SELECT * FROM books_s2;

```

```output

+-----------------+----------------+-------------+
| title_s2        | author_s2      | numpages_s2 |
+-----------------+----------------+-------------+
| Onyx Storm      | Rebecca Yarros | 544         |
| The Maid        | Nita Prose     | 385         |
| The Last Letter | Rebecca Yarros | 432         |
+-----------------+----------------+-------------+
```

In this example, SingleStore processes JSON files that contain a top-level array.

* The pipeline loads each object in the array as a separate row.
* SingleStore pipelines support top-level JSON arrays for both `INTO TABLE` and `INTO PROCEDURE` targets.
* You can also load the same JSON file by using `LOAD DATA … FORMAT JSON`.

## Extract Nested JSON Fields Using a Pipeline

This example uses `books.json` and creates a pipeline that extracts values from nested JSON fields using the `::` subvalue path syntax. Refer to [Map JSON Fields to Multiple Columns](https://docs.singlestore.com/#section-id235621890940097.md) to view the data structure of books.json file.

Create a table named `books_nested`.

```sql
CREATE TABLE books_nested (
    title_s2 TEXT,
    author_s2 TEXT,
    publisher_s2 TEXT,
    numpages_s2 INT,
    printings_s2 JSON,
    series_s2 TEXT
);
```

Create a pipeline named `books_nested_pipe`.

```sql
CREATE PIPELINE books_nested_pipe AS
LOAD DATA S3 's3://singlestore-docs-example-datasets/json/books.json'
INTO TABLE books_nested (
    title_s2 <- title,
    author_s2 <- author,
    publisher_s2 <- details::publisher,
    numpages_s2 <- details::numpages,
    printings_s2 <- details::printings,
    series_s2 <- details::series DEFAULT NULL
)
FORMAT JSON;
```

Start the pipeline:

```sql
START PIPELINE books_nested_pipe FOREGROUND;
```

Verify the results:

```sql
SELECT * FROM books_nested;

```

```output

+-----------------+----------------+---------------------------+-------------+----------------+--------------+
| title_s2        | author_s2      | publisher_s2              | numpages_s2 | printings_s2   | series_s2    |
+-----------------+----------------+---------------------------+-------------+----------------+--------------+
| Onyx Storm      | Rebecca Yarros | Entangled:Red Tower Books | 544         | [2,1.3]        | The Empyrean |
| The Maid        | Nita Prose     | Ballantine Books          | 385         | [0.5,0.75,1.2] | NULL         |
| The Last Letter | Rebecca Yarros | Entangled:Amara           | 432         | [0.25,0.5,0.5] | NULL         |
+-----------------+----------------+---------------------------+-------------+----------------+--------------+
```

In this example:

* The `details::publisher` , `details::numpages`, `details::printings`, and `details::series` subvalue paths extract values from the nested details object.
* The `::` separator navigates nested JSON objects. For deeper nesting, chain additional identifiers (for example, `level1::level2::level3`).
* SingleStore assigns a `DEFAULT NULL` value to `series_s2` because not all JSON values contain a series field in the details object.

***

Modified at: June 11, 2026

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

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