Load JSON Files Examples

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 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;

+-----------------+----------------+---------------------------+-------------+---------------------+----------------+--------------+
| 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 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;

+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
| 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;

+-----------------+----------------+-------------+
| 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 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;

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

Load JSON Files Examples

On this page

Map JSON Fields to Multiple Columns

Load Entire JSON Values into a Single Column

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

Load a JSON File with a Top-Level Array

Extract Nested JSON Fields Using a Pipeline

Was this article helpful?

On this page

Was this article helpful?

Load JSON Files Examples

On this page

Map JSON Fields to Multiple Columns

Load Entire JSON Values into a Single Column

Use DEFAULT, SET, and WHERE Clauses

Load a JSON File with a Top-Level Array

Extract Nested JSON Fields Using a Pipeline

Was this article helpful?

On this page

Was this article helpful?

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