Important

The SingleStore 9.0 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Load JSON Files Examples

{
       "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]
       }
}

Example 1: Map JSON Fields to Multiple Columns

Map the JSON fields to multiple columns.

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 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_2 column as not all JSON values have a series field.

This example can be run as is (without providing credentials), as the books.json data file is in a public bucket.

Here is the resulting table.

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

Example 2: Load JSON Value into a Single Column

This example loads the JSON values into a single column using the subvalue_mapping: singlestore_col_name <- %.

SQL

CREATE TABLE books_json_single_col (json JSON);

LOAD DATA S3 's3://singlestore-docs-example-datasets/json/books.json'
INTO TABLE books_json_single_col
(json <- %)
FORMAT JSON;

This example can be run as is (without providing credentials), as the books.json data file is in a public bucket.

Here is the resulting table.

SQL

SELECT JSON_PRETTY(json)
FROM books_json_single_col;

-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(json)                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "author": "Rebecca Yarros",
  "details": {
    "numpages": 432,
    "printings": [
      0.25,
      0.5,
      0.5
    ],
    "publication date": "February 26, 2019",
    "publisher": "Entangled:Amara"
  },
  "title": "The Last Letter"
}                     |
| {
  "author": "Rebecca Yarros",
  "details": {
    "numpages": 544,
    "printings": [
      2,
      1.3
    ],
    "publication date": "January 21, 2025",
    "publisher": "Entangled:Red Tower Books",
    "series": "The Empyrean"
  },
  "title": "Onyx Storm"
} |
| {
  "author": "Nita Prose",
  "details": {
    "numpages": 385,
    "printings": [
      0.5,
      0.75,
      1.2
    ],
    "publication date": "January 4, 2022",
    "publisher": "Ballantine Books"
  },
  "title": "The Maid"
}                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Note: The JSON_PRETTY function has been used to format the JSON values. JSON_PRETTY outputs a string not a JSON value, it is intended to make data human-readable.

Example 3: Defaults, SET, and Escapes

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.