# 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.0/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.0/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 approach is useful when the JSON structure is known and consistent and you want to query individual fields efficiently, apply indexes, or use fields in shard or sort keys.

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. This approach is useful when you want to preserve the full JSON document for flexible querying and schema evolution. Refer to [Map JSON Fields to Multiple Columns](https://docs.singlestore.com/#section-id235621890940097.md) to view the data structure of `books.json` file.

Loading the entire JSON object into a single column is useful when:

* Records have varying schemas (different fields per record).
* You want to ingest data quickly without defining a rigid schema upfront.
* The JSON structure may evolve over time.
* You plan to extract fields at query time using the `::` or `::$` operators.

Use the `%` symbol in the subvalue mapping to capture the complete JSON value.

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.

After loading the data, extract fields at query time using keypath operators:

```sql
SELECT json::$title AS title,
       json::$author AS author,
       json::details::%numpages AS pages
FROM books_json_single_col;

```

```output

+-----------------+----------------+-------+
| title           | author         | pages |
+-----------------+----------------+-------+
| Onyx Storm      | Rebecca Yarros |  544 |
| The Last Letter | Rebecca Yarros |  432 |
| The Maid        | Nita Prose     |  385 |
+-----------------+----------------+-------+
```

Filter on JSON values without extracting all fields:

```sql
SELECT json::$title AS title
FROM books_json_single_col
WHERE json::details::%numpages > 400;

```

```output

+-----------------+
| title           |
+-----------------+
| Onyx Storm      |
| The Last Letter |
+-----------------+
```

> **📝 Note**: Loading data into a single JSON column is simpler to set up but may result in slower query performance for filtered reads compared to mapped columns with indexes. For frequently filtered or joined fields, consider a combined approach that maps those fields to dedicated columns while also storing the full JSON document in a separate column:```sql
> LOAD DATA INFILE '/path/to/books.json'
> INTO TABLE books_combined
> FORMAT JSON (
>     title <- title,
>     author <- author,
>     full_record <- %
> );
> ```

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

## Load JSON Data from Kafka into a Table Using ON DUPLICATE KEY UPDATE

Kafka uses at-least-once delivery semantics, so the same event can be delivered more than once. As a consequence, if a pipeline loads duplicate events into a table that have the same primary key, duplicate key conflicts can occur.

This example creates a Kafka pipeline that ignores duplicate events by using the `SKIP DUPLICATE KEY ERRORS` clause.

In this example, the Kafka topic contains the following events:

```json
{"customer_id":1,"name":"Alice","email":"alice@example.com","address":{"city":"Seattle"}}
{"customer_id":1,"name":"Alice","email":"alice@example.com","address":{"city":"Seattle"}}
{"customer_id":2,"name":"Bob","email":"bob@example.com","address":{"city":"Boston"}}
```

Create a table named `customers`:

```sql
CREATE TABLE customers (
    customer_id BIGINT NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255),
    address JSON,
    updated_at DATETIME(6) DEFAULT NOW(),
    PRIMARY KEY (customer_id)
);
```

Create a pipeline named `customers_pipeline`:

```sql
CREATE PIPELINE customers_pipeline AS
LOAD DATA KAFKA 'broker:9092/customers_topic'
BATCH_INTERVAL 2500
SKIP DUPLICATE KEY ERRORS
INTO TABLE customers
FORMAT JSON (
    customer_id <- customer_id,
    name <- name,
    email <- email,
    address <- address
);
```

Start the pipeline:

```
START PIPELINE customers_pipeline;
```

Verify the results:

```sql
SELECT * FROM customers;

```

```output

+-------------+-------+-------------------+----------------------+----------------------------+
| customer_id | name  | email             | address              | updated_at                 |
+-------------+-------+-------------------+----------------------+----------------------------+
|           1 | Alice | alice@example.com | {"city":"Seattle"}   | 2026-07-15 10:21:34.123456 |
|           2 | Bob   | bob@example.com   | {"city":"Boston"}    | 2026-07-15 10:21:34.456789 |
+-------------+-------+-------------------+----------------------+----------------------------+
```

In this example:

* The pipeline loads the first event for each `customer_id`.
* When a subsequent event has the same primary key, the `SKIP DUPLICATE KEY ERRORS` clause ignores it instead of generating an error.
* This option is useful when duplicate Kafka events contain identical data and you want to preserve the first record that the pipeline ingests.

## Related Topics

* [Using JSON](https://docs.singlestore.com/db/v9.0/create-a-database/using-json.md)
* [Load JSON Files with LOAD DATA](https://docs.singlestore.com/db/v9.0/load-data/load-data-from-files/load-data-from-json-files/load-json-files-with-load-data.md)
* [JSON Functions](https://docs.singlestore.com/db/v9.0/reference/sql-reference/json-functions.md)

***

Modified at: October 8, 2026

Source: [/db/v9.0/load-data/load-data-from-files/load-data-from-json-files/load-json-files-examples/](https://docs.singlestore.com/db/v9.0/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)