Example for JSON data: Create and load a time series with JSON documents

These examples show how to create, load, and query a time series that stores JSON data.

About this task

The way that you load time series data with JSON documents depends on whether the data is a hybrid of text data and JSON documents, or completely within JSON documents:

Example: Load hybrid data that includes JSON documents
Example: Load pure JSON documents

Example: Load hybrid data that includes JSON documents

About this task

In this example, you create a time series that contains meter readings. The meter ID and time stamp values are in plain text format and the sensor readings are in JSON documents. Readings are taken every 15 minutes. The following table lists the time series properties that are used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Timepoint size	15 minutes
When timepoints are valid	Every 15 minutes with no invalid times
Data in the time series	Timestamp JSON documents that are stored in a BSON column in the TimeSeries subtype
Time series table	A meter ID column of type INTEGER A TimeSeries data type column
Origin	2014-01-01 00:00:00.00000
Regularity	Regular
Metadata	No metadata
Where to store the data	In a container that you create
How to load the data	Through an external table and a loader program
How to access the data	Through a virtual table

Procedure

To create, load, and query a time series that contains JSON data:

Create a TimeSeries subtype that is named ts_data_j in a database by running the following SQL statement:
```
CREATE ROW TYPE ts_data_j(
      tstamp       datetime year to fraction(5),
      sensor_data  BSON
);
```
Because the JSON documents are stored in the database as BSON documents, the sensor_data column has a BSON data type.

Create a time series table that is named tstable_j by running the following SQL statement:

CREATE TABLE IF NOT EXISTS tstable_j(
   id  INT NOT NULL PRIMARY KEY,
   ts  timeseries(ts_data_j)
) LOCK MODE ROW;

Create a container that is named container_b in a dbspace by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSContainerCreate('container_b', 'dbspace1', 'ts_data_j', 512, 512);
```

Create a time series with a JSON document by running the following SQL statement:

INSERT INTO tstable_j VALUES(1, 'origin(2014-01-01 00:00:00.00000), 
                             calendar(ts_15min), container(container_b),
                             regular, threshold(0),
                             [({"v1":1.5, "v2":20.5})]');

A predefined calendar with 15-minute intervals is specified. The JSON document contains two values.

Create a pipe-delimited file in any directory with the name json.unl that contains the time series data to load:

1|"2014-01-01 00:00:00.00000"|{"v1":2.0, "v2":17.4}|
1|"2014-01-01 00:30:00.00000"|{"v1":1.9, "v2":20.2}|
1|"2014-01-01 00:45:00.00000"|{"v1":1.8, "v2":19.7}|

Create an external table and load it with time series data from the json.unl file:

CREATE EXTERNAL TABLE ext_tstable_j
(
id         INT,
tstamp     DATETIME YEAR TO FRACTION(5),
json_doc   JSON
)
USING(
  FORMAT 'DELIMITED',
  DATAFILES
    (
      "DISK:path/json.unl" 
     )
);

Substitute path with the directory for the json.unl file.

Initialize a global context and open a database session by running the TSL_Init function:
```
EXECUTE FUNCTION TSL_Init('tstable_j','ts');
```
Load the data by running the TSL_PutSQL function with an SQL statement that selects the data from the external table and casts the JSON column to BSON:
```
EXECUTE FUNCTION TSL_PutSQL('tstable_j|ts',
         "SELECT id, tstamp, json_col::bson FROM ts_exttable_j2");
```

Save the data to disk by running the TSL_FlushAll function:

BEGIN;
EXECUTE FUNCTION TSL_FlushAll('tstable_j|ts');
COMMIT WORK;

Close the session and remove the global context by running the TSL_SessionClose function and the TSL_Shutdown procedure:
```
EXECUTE FUNCTION TSL_SessionClose('tstable_j|ts');
EXECUTE PROCEDURE TSL_Shutdown('tstable_j|ts');
```
Create a virtual table that is named virt_tstable_j by running the TSCreateVirtualTab procedure:
```
EXECUTE PROCEDURE TSCreateVirtualTab("virt_tstable_j", "tstable_j");
```

View the virtual table by running a SELECT statement. Cast the sensor_data column to JSON so that you can view the data:

SELECT id, tstamp, sensor_data::JSON FROM virt_tstable_j;

(expression)
id       tstamp                       sensor_data
1        2014-01-01 00:00:00.00000    {"v1":1.5, "v2":20.5}
1        2014-01-01 00:15:00.00000    {"v1":2.0, "v2":17.4}
1        2014-01-01 00:30:00.00000    {"v1":1.9, "v2":20.2}
1        2014-01-01 00:45:00.00000    {"v1":1.8, "v2":19.7}

4 row(s) retrieved.

Insert a row through the virtual table. You must explicitly cast the JSON data to the JSON data type, and then cast the data to the BSON data type:
```
INSERT INTO virt_tstable_j values(1, "2014-01-01 01:00:00.00000",
('{"v1":2.1, "v2":20.1}'::JSON)::BSON);
```
Important: You do not receive an error if you do not cast the JSON data to the JSON data type. The data is inserted as BSON data, but you can no longer view the data in JSON format.

Example: Load pure JSON documents

About this task

In this example, you create a time series that contains meter readings. The meter IDs, time stamps, and meter readings are in JSON documents. Readings are taken every 15 minutes. The following table lists the time series properties that are used in this example.

Table 2. Time series properties used in this example
Time series property	Definition
Timepoint size	15 minutes
When timepoints are valid	Every 15 minutes with no invalid times
Data in the time series	Timestamp JSON documents that are stored in a BSON column in the TimeSeries subtype
Time series table	A meter ID column of type INTEGER A TimeSeries data type column
Origin	2014-01-01 00:00:00.00000
Regularity	Regular
Metadata	No metadata
Where to store the data	In a container that you create
How to load the data	Through a loader program

Procedure

To load pure JSON documents into a time series:

Allow newline characters by running the following statement:
```
EXECUTE PROCEDURE ifx_allow_newline('t');
```
Create a TimeSeries subtype that is named ts_data_j2 in a database by running the following SQL statement:
```
CREATE ROW TYPE ts_data_j2(
      tstamp       datetime year to fraction(5),
      sensor_data  BSON
);
```
Because the JSON documents are stored in the database as BSON documents, the sensor_data column has a BSON data type.

Create a time series table that is named tstable_j2 by running the following SQL statement:

CREATE TABLE IF NOT EXISTS tstable_j2(
   id  INT NOT NULL PRIMARY KEY,
   ts  timeseries(ts_data_j2)
) LOCK MODE ROW;

Create a container that is named container_j in a dbspace by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSContainerCreate('container_j', 'dbspace1', 'ts_data_j2', 512, 512);
```

Create the time series by running the following SQL statement:

INSERT INTO tstable_j2 VALUES(1, 'origin(2014-01-01 00:00:00.00000), 
                             calendar(ts_15min), container(container_j),
                             regular, threshold(0), []');

A predefined calendar with 15-minute intervals is specified.

Create a pipe-delimited file in any directory with the name json2.unl that contains three JSON documents to load:

{"id":1, "tstamp":"2014-01-01 00:15:00.00000", "v1":2.0, "v2":17.4}
{"id":1, "tstamp":"2014-01-01 00:30:00.00000", "v1":1.9, "v2":20.2}
{"id":1, "tstamp":"2014-01-01 00:45:00.00000", "v1":1.8, "v2":19.7}

The JSON documents are separated by newline characters.

Initialize a global context and open a database session by running the TSL_Init function:
```
EXECUTE FUNCTION TSL_Init('tstable_j2','ts');
```
Load the data by running the TSL_PutJson function with an SQL statement that selects the data from the file:
```
EXECUTE FUNCTION TSL_PutJson('tstable_j2|ts',
         "DISK:path/json2.unl");
```
Substitute path with the directory for the json2.unl file.

Save the data to disk by running the TSL_FlushAll function:

BEGIN;
EXECUTE FUNCTION TSL_FlushAll('tstable_j2|ts');
COMMIT WORK;

Close the session and remove the global context by running the TSL_SessionClose function and the TSL_Shutdown procedure:
```
EXECUTE FUNCTION TSL_SessionClose('tstable_j2|ts');
EXECUTE PROCEDURE TSL_Shutdown('tstable_j2|ts');
```
Create a virtual table that is named virt_tstable_j2 by running the TSCreateVirtualTab procedure:
```
EXECUTE PROCEDURE TSCreateVirtualTab("virt_tstable_j2", "tstable_j2");
```

View the virtual table by running a SELECT statement. Cast the sensor_data column to JSON so that you can view the data:

SELECT id, tstamp, sensor_data::JSON FROM virt_tstable_j2;

(expression)
id tstamp                     sensor_data
1  2014-01-01 00:00:00.00000  {"id":1, "ts":2014-01-01 00:15:00.00000,  
                               "v1":2.0, "v2":17.4}
1  2014-01-01 00:15:00.00000  {"id":1, "ts":2014-01-01 00:30:00.00000,  
                               "v1":1.9, "v2":20.2}
1  2014-01-01 00:30:00.00000  {"id":1, "ts":2014-01-01 00:45:00.00000,  
                               "v1":1.8, "v2":19.7}

3 row(s) retrieved.

Although the meter ID and the time stamp are copied from the JSON documents and inserted into the id and tstamp columns, the entire JSON documents are included in the sensor_data column.