Methods for loading JSON data

The type of data you have depends on whether some or all of the time series data is in JSON documents:

• All the data is in JSON documents: Pure JSON documents contain the primary key values, time stamp values, and values for a BSON column in the TimeSeries data type. For example, the following JSON document contains the primary key value, the time stamp, and other values:

  {"id":"met0000000","tstamp":2014-01-01 00:00:00.00000,"m1":-13.00,"m2":100.00}

• Some of the data is in JSON documents: Hybrid data is a combination of the primary key and time stamp values in plain text format and JSON documents that contain values for a BSON column in the TimeSeries data type. Values for other columns can also be included in plain text format. For example, the following text has a meter ID, a time stamp, and a JSON document that contains other values:

  met0000000|2014-01-01 00:00:00.00000|{"m1":-13.00,"m2":100.00}

To load pure JSON documents, write a loader program that uses the TSL_PutJson function to load data from the following sources:

• A file of JSON documents
• A named pipe that streams JSON documents

To load hybrid data, use one of the following methods:

• Load the entire contents of a file: Write a loader program that uses the TSL_Put function.
• Load selected contents of a file: Create an external table that is based on the file and write a loader program that uses the TSL_PutSQL function.
• Insert individual rows:
  • Run the time series input function. The database server automatically casts the JSON documents to BSON.
  • Run time series functions like PutElem and InsElem.
• Insert through a virtual table: Insert individual rows with INSERT statements into a virtual table that is based on a time series that contains a BSON column. You must explicitly cast the JSON data to the JSON data type, and then cast the data to the BSON data type. When you query a virtual table, you must cast the contents of the BSON column to JSON to view the data.