# 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.

## Flatten a Nested JSON Array Using a Pipeline Stored Procedure

This example creates a pipeline stored procedure that flattens a JSON array nested inside each input record into one row per array element, while preserving fields from the surrounding object.

A pipeline that targets a table with `FORMAT JSON` automatically expands a top-level JSON array into one row per element. When the array is nested inside an outer object, an `INTO TABLE` pipeline cannot flatten it by itself and a column mapping such as `model <- models::model` returns only the last array element. To split such an array into rows and join its values with the outer fields, use a [pipeline stored procedure](https://docs.singlestore.com/db/v9.1/reference/sql-reference/pipelines-commands/create-pipeline-into-procedure.md) and the `JSON_TO_ARRAY` function.

The `companies.json` file used in this example contains one JSON object per line, where each object describes a company and the array of car models it produces:

```json
{"id": 1, "make": "Toyota", "models": [{"model": "Camry", "year": 2020}, {"model": "Corolla", "year": 2021}]}
{"id": 2, "make": "Honda",  "models": [{"model": "Civic", "year": 2019}, {"model": "Accord",  "year": 2022}]}
```

Create a table named `companies`. Each row stores the parent company fields alongside one extracted model.

```sql
CREATE TABLE companies (
    id     INT,
    make   VARCHAR(50),
    models JSON,
    model  VARCHAR(50),
    year   INT
);
```

Create a stored procedure named `companies_proc`.

```sql
DELIMITER //

CREATE OR REPLACE PROCEDURE companies_proc(batch QUERY(payload JSON NOT NULL))
AS
BEGIN
    INSERT INTO companies(id, make, models, model, year)
    SELECT payload::id,
           payload::$make,
           payload::models,
           table_col::$model,
           table_col::$year
    FROM batch
        JOIN TABLE(JSON_TO_ARRAY(payload::models));
END //

DELIMITER ;
```

Create a pipeline named `companies_pipe`.

```sql
CREATE PIPELINE companies_pipe
AS LOAD DATA S3 's3://singlestore-docs-example-datasets/json/companies.json'
INTO PROCEDURE companies_proc
(payload <- %)
FORMAT JSON;
```

Start the pipeline.

```sql
START PIPELINE companies_pipe FOREGROUND;
```

Verify the results:

```sql
SELECT id, make, model, year FROM companies ORDER BY id, year;

```

```output

+----+--------+---------+------+
| id | make   | model   | year |
+----+--------+---------+------+
|  1 | Toyota | Camry   | 2020 |
|  1 | Toyota | Corolla | 2021 |
|  2 | Honda  | Civic   | 2019 |
|  2 | Honda  | Accord  | 2022 |
+----+--------+---------+------+
```

In this example:

* `JSON_TO_ARRAY(payload::models)` produces one row per element of the models array.
* `table_col` is the implicit column name for each flattened array element. The `::$<key>` operator extracts each element's fields as scalar values.
* The outer-object fields (`payload::id`, `payload::$make`, `payload::models`) are repeated on every row produced from the same input record, so each output row preserves its parent company alongside one extracted model.
* The (`payload <- %`) mapping binds the full JSON value of each input record to the procedure parameter. The stored procedure performs the flattening, therefore individual per-field mapping is not required in the pipeline definition.
* The original `models` array is preserved as JSON in the `models` column. Remove this column from both the table definition and the `INSERT` list if you do not need to retain the array on every row.

***

Modified at: July 9, 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)