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:

JSON

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

Create a table named t.

SQL

CREATE TABLE t(a INT);

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

SQL

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

Verify the results:

SQL

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:

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 JSON Files Examples

On this page

Load Nested JSON Values

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

Was this article helpful?

On this page

Was this article helpful?

Load JSON Files Examples

On this page

Load Nested JSON Values

Use DEFAULT, SET, and WHERE Clauses

Was this article helpful?

On this page

Was this article helpful?

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