Load JSON Files Examples

Example 1

If example.json consists of:

JSON

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

Then it can be loaded as follows:

SQL

CREATE TABLE t(a INT);

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

SELECT * FROM t;

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

Example 2

If example2.json consists of:

JSON

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

Then we can perform a more complicated LOAD DATA:

SQL

CREATE TABLE t(b bool NOT NULL, s TEXT, n DOUBLE, a INT, o JSON NOT NULL, whole longblob);

LOAD DATA LOCAL INFILE "example2.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;

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

There are several things to note in the example above:

true was converted to "1" for columns b, but not for column whole. "1" was further converted to the BOOL value 1.
The escapes"\u00AE" and "\u0022" were converted to UTF-8 for column s, but not for column whole. Note that whole would have become invalid JSON if we had translated "\u0022".
The second row was discarded because it failed to match the WHERE clause.
None of the paths in subvalue_mapping could be found in the third row, so DEFAULT literals like '{"subobject":"replaced"}' were assigned instead.
We assigned a to an intermediate variable so that we could extract an array element in the SET clause.
The top-level JSON values in example2.json were not all JSON objects. "hello" is a valid top-level JSON value.

Load JSON Files Examples

On this page

Example 1

Example 2

Was this article helpful?

On this page

Was this article helpful?