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
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 exampleTime 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 exampleTime 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.
Have feedback?
Google Analytics is used to store comments and ratings. To provide a comment or rating for a topic, click Accept All Cookies or Allow All in Cookie Preferences in the footer of this page.