Sign inTry Free

Load JSON Files Examples

On this page

Note

For more information on the LOAD DATA command, refer to LOAD DATA.

Examples 1 and 2 show two different ways to load a JSON file into SingleStore:

  1. Map fields in the JSON file to columns in a table.

  2. Load JSON values into a single column.

Example 3 is an example of loading more complex JSON with defaults, SET, and escapes

The following JSON is used in Examples 1 and 2.

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

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.

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

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.

SELECT JSON_PRETTY(json_col)
FROM books_single_col;
-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(json_col)                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "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

If example2.json consists of:

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

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.

To use an ENCLOSED BY <char> as a terminating field, a TERMINATED BY clause is needed. For clarity, instances of an ENCLOSED BY <char> appearing within a field value can be duplicated, and they will be understood as a singular occurrence of the character.

If an ENCLOSED BY "" is used, quotes are treated as follows:

  • "The ""NEW"" employee"  → The "NEW" employee

  • The "NEW" employee → The "NEW" employee

  • The ""NEW"" employee → The ""NEW"" employee

Example 1

If example.json consists of:

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

Then it can be loaded as follows:

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:

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

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.

Last modified: September 10, 2025

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK