Load JSON Files Examples

Load Nested JSON Values

This example loads nested JSON values into a single table column using the :: subvalue path syntax.

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

{"a":{"b":1}, "c":null}
{"a":{"b":2}, "d":null}

Create a table named t.

CREATE TABLE t(a INT);

Load the nested JSON value into column a of the table t.

LOAD DATA LOCAL INFILE "nested_values.json"
INTO TABLE t(a <- a::b)
FORMAT JSON;

Verify the results:

SELECT * FROM t;

+------+
| a    |
+------+
|    1 |
|    2 |
+------+

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:

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

Create the table named t.

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.

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:

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.

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

{"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.

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

Create a stored procedure named companies_proc.

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.

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.

START PIPELINE companies_pipe FOREGROUND;

Verify the results:

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

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