Warning

SingleStore 9.0 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.

Using Mapping with JSON files

Examples

The following examples use a JSON key pair and an array from a local file source.

SQL

CREATE TABLE keypairs(`key` VARCHAR(10), `value` VARCHAR(10)); 
CREATE PIPELINE jtest AS
LOAD DATA FS '<file path>/keypairs.json'
INTO TABLE keypairs
FORMAT JSON(`key` <- keypairs::`key`,`value` <- keypairs::`value`); 

START PIPELINE jtest;

SELECT * FROM keypairs;

+------+--------+
| key  | value  | 
+------+--------+
|    1 | 1      |
+------+--------+

Warning

To use reserved words in a table, backticks are required. The example above uses key and value as column names by using backticks.

SQL

CREATE TABLE teams(basketball VARCHAR(50), baseball VARCHAR (50), 
football VARCHAR(50), hockey VARCHAR(50));

CREATE PIPELINE teams_list AS
LOAD DATA FS '<file path>/jtinsert.json'
INTO TABLE teams
FORMAT JSON
(basketball <- teams::basketball,
baseball <- teams::baseball,
football <- teams::football,
hockey <- teams::hockey);

START PIPELINE teams_list;

SELECT * FROM teams;

+------------+-----------+----------+--------+
| basketball | baseball  | football | hockey |
+------------+-----------+----------+--------+
|  Lakers    | Dodgers   | Raiders  | Kings  |
+------------+-----------+----------+--------+

The following examples use a JSON key pair and an array from an S3 bucket.

SQL

CREATE TABLE keypairs(`key` VARCHAR(10), `value` VARCHAR (10));

CREATE PIPELINE jtest AS
LOAD DATA S3 's3://<bucket_name>/<filename>.json'
CONFIG '{"region":"us-west-2"}'
CREDENTIALS '{"aws_access_key_id": "XXXXXXXXXXXXXXXXX",               
              "aws_secret_access_key": "XXXXXXXXXXX"}'
INTO TABLE keypairs
FORMAT JSON
(`key` <- keypairs::`key`,
`value` <- keypairs::`value`);

START PIPELINE jtest;
SELECT * FROM keypairs;

+------+--------+
| key  | value  | 
+------+--------+
|    1 | 1      |
+------+--------+

SQL

CREATE TABLE teams(basketball varchar(50), baseball varchar (50), 
football varchar(50), hockey varchar(50));

Below is the contents of the JSON file used in the pipeline example:

JSON

{
	"teams": [{
			"basketball": "Knicks"
		},
		{
			"baseball": "Yankees"
		},
		{
			"football": "Giants"
		},
		{
			"hockey": "Rangers"
		}
	]
}

SQL

CREATE PIPELINE teams_list AS 
LOAD DATA S3 's3://<bucket_name>/<filename>.json'
CONFIG '{"region":"us-west-2"}'
CREDENTIALS '{"aws_access_key_id": "XXXXXXXXXXXXXXXXX",               
              "aws_secret_access_key": "XXXXXXXXXXX"}'
INTO TABLE teams
FORMAT JSON
(basketball <- teams::basketball,
baseball <- teams::baseball,
football <- teams::football,
hockey <- teams::hockey);

START PIPELINE teams_list;
SELECT * FROM teams;

+------------+-----------+----------+--------+
| basketball | baseball  | football | hockey |
+------------+-----------+----------+--------+
|  Lakers    | Dodgers   | Raiders  | Kings  |
+------------+-----------+----------+--------+

Using Mapping with JSON files

On this page

Examples

Was this article helpful?

On this page

Was this article helpful?