# Load JSON Files with LOAD DATA

Data from JSON files can be loaded into SingleStore using the [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md) command. 

JSON files can be loaded into SingleStore tables in two ways:

* Fields in the JSON can be mapped to columns in a SingleStore table using `FORMAT JSON` and a `subvalue_mapping`.

  * SQL queries over the columns are used to query the JSON data.
* Entire JSON values can be loaded in a single `JSON` type column in SingleStore using `FORMAT JSON` and the `<- %` `subvalue_mapping`. Using the `JSON` type provides:

  * [Columnarized storage](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/columnstore-seekability-using-json.md) for efficient analytics.
  * [JSON Functions](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions.md) to extract, search and modify the data.
  * Full-text search, [computed columns](https://docs.singlestore.com/db/v9.1/create-a-database/using-json/#UUID-2ccafa6b-ead7-016d-adca-79fb8d0bdade.md), and [multi-value indexing](https://docs.singlestore.com/db/v9.1/create-a-database/multi-value-hash-index-json.md) to optimize lookups.
  * Refer to [Using JSON](https://docs.singlestore.com/db/v9.1/create-a-database/using-json.md) for an overview and examples.

For more information on the `LOAD DATA` command, refer to [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md). For examples of using `LOAD DATA` with JSON files, refer to [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.md).

## Syntax

```sql
LOAD DATA [LOCAL] INFILE 'file_name'
  [REPLACE | SKIP { CONSTRAINT | DUPLICATE KEY } ERRORS]
  INTO TABLE tbl_name
  FORMAT JSON
  subvalue_mapping
  [SET col_name = expr,...]
  [WHERE expr,...]
  [MAX_ERRORS number]
  [ERRORS HANDLE string]

subvalue_mapping:
  ( {col_name | @variable_name} <- subvalue_path [DEFAULT literal_expr], ...)

subvalue_path:
  {% | [%::]ident [::ident ...]}

```

## Map JSON Fields to Columns

Use the `FORMAT JSON` syntax and a `subvalue_mapping` to map fields from JSON values to columns in SingleStore.

When this syntax is used, for each JSON value in the file `file_name`, the values in the fields specified in the `subvalue_mapping` are extracted and the extracted subvalues are assigned to either:

* A column of a row in the SingleStore table `tbl_name`.
* A variable which is further used in the `SET` clause to assign a value to a column in a row in the SingleStore table. 
* If a specified subvalue cannot be found in the input JSON, the engine will assign the `DEFAULT` clause literal instead.
* If a column is not mentioned in the subvalue mapping, all values of that column will be set to NULL.

In the following example:

* The values from `json_subvalue_path1` and `json_subvalue_path2` are assigned to the columns `singlestore_col_name1`, `singlestore_col_name2`, respectively.
* If no value is found for `json_subvalue_path2`, `singlestore_col_name2` is set to the `DEFAULT` value `42`.
* The value from `json_subvalue_path3` is assigned to the variable `@varname`, and the `SET` statement assigns the result of the expression on `@varname` (`expr(@varname)`) to `singlestore_col_name3`.

```sql
LOAD DATA INFILE 'filename' 
INTO TABLE tbl_name
( 
   singlestore_col_name1 <- json_subvalue_path1,
   singlestore_col_name2 <- json_subvalue_path2 DEFAULT 42,
   @varname <- json_fieldname3
)
SET singlestore_colname3 = expr(@varname)
FORMAT JSON;
```

## Load Entire JSON Value to a Single Column

Use `FORMAT JSON` and the `<- %` subvalue\_mapping to load entire JSON values into a single SingleStore column.

In the following example, each JSON value in the file will be loaded into a column in a row in the table `tbl_name`.

```sql
LOAD DATA INFILE 'filename' 
INTO TABLE tbl_name
( 
   singlestore_col_name <- %
)
FORMAT JSON;
```

## Remarks

* To specify the compression type of an input file, use the `COMPRESSION` clause. See [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data/#UUID-55530b3c-3939-f404-a8e0-db8a648b36fd.md) for more information.
* The file named by `file_name` must consist of concatenated UTF-8 encoded JSON values, optionally separated by whitespace. Newline-delimited JSON is accepted, for example.
* Non-standard JSON values like `NaN`, `Infinity`, and `-Infinity` must not occur in `file_name`.
* If `file_name` ends in `.gz` or `.lz4`, it will be decompressed.
* JSON `LOAD DATA` supports a subset of the error recovery options allowed by CSV `LOAD DATA`. Their behavior is as described under [Load CSV Files with LOAD DATA](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-csv-files/load-csv-files-with-load-data/#section-idm4319172807080517.md).
* Like CSV `LOAD DATA`, JSON `LOAD DATA` allows you to use globbing to load data from multiple files.
* Writing to multiple databases in a transaction is not supported.
* The engine discards rows that do not match the `WHERE` clause.

## LOAD DATA from an AWS S3 Source

JSON files that are stored in an AWS S3 bucket can be loaded via a LOAD DATA query without a pipeline.

```sql
LOAD DATA S3 '<bucket name>'
CONFIG '{"region" : "<region_name>"}' 
CREDENTIALS '{"aws_access_key_id" : "<key_id>", 
             "aws_secret_access_key": "<access_key>"}' 
INTO TABLE <table_name>;

```

***

Modified at: June 10, 2026

Source: [/db/v9.1/load-data/load-data-from-files/load-data-from-json-files/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/)

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