Formatting Data as JSON

s-Server's JSON formatter converts rows from s-Server into JavaScript Object Notation (JSON) based on options supplied through the options clause of a foreign table or foreign stream.

When using SQL, the JSON formatter converts rows to JSON that you define in a foreign table or foreign stream, outputting these rows to a data writer that you have defined for the foreign table or stream. When you INSERT into the foreign table or stream, s-Server begins writing JSON to the data writer that you specify.

When using the ECD agent, the JSON converts rows that you define in a properties file to JSON, outputting these rows to a data writer that you have defined for the foreign table or stream.

For a list of writers, and details on how to use them, see the Writing to Other Destinations.

For performance reasons, most formatting should be done in native SQL and passed into ECDA, not pushed into ECDA formatters.

Using SQL to Write JSON

To write JSON data, you create a foreign table or stream that references one of s-Server's prebuilt server objects. Like all tables and streams, foreign tables and streams must be created within a schema. The example below creates and sets a schema called json_data and creates a foreign stream called json_writer_stream that writes data to the file system.

To write data over other input/output systems, such as Kafka, Kinesis, a network socket, a WebSocket, HTTP or AMQP, you would need to specify options for these formats. See Writing to Other Destinations for more details.

CREATE OR REPLACE SCHEMA json_data;
SET SCHEMA 'json_data';

CREATE OR REPLACE FOREIGN STREAM json_writer_stream
("STR1" VARCHAR(32))
SERVER FILE_SERVER
("id" DOUBLE,
"reported_at" VARCHAR(4096),
"shift_no" DOUBLE,
"trip_no" DOUBLE,
"route_variant_id" VARCHAR(4096),
"waypoint_id" DOUBLE,
"last_known_location_state" VARCHAR(4096),
"subroute_id" VARCHAR(50),
"descriptions" VARCHAR(64)
 )
 SERVER FILE_SERVER

 OPTIONS
 (
  --file directory where JSON file will be written.
  "DIRECTORY" '/*path/to/myfile',
  "FORMATTER" 'JSON',
  "CHARACTER_ENCODING" 'UTF-8',
  "ORIGINAL_FILENAME" 'stocks-output.csv',
  "FILENAME_PREFIX" 'output-',
  "FILENAME_SUFFIX" '.json',
  "FILENAME_DATE_FORMAT" 'yyyy-MM-dd-HH:mm:ss',
  "FILE_ROTATION_SIZE" '20K'
  "FORMATTER_INCLUDE_ROWTIME" 'true',
  "PATH_subroute_id" 'duplicate.field.path',
  "PATH_id" 'duplicate.field.path',
  "SCHEMA_FILE_LOCATION" '/path/of/schemafile.json'
  );

To actually write to a file in path/to/myfile, you need to write a pump containing an INSERT statement along the following lines. For more information on pumps, see the topic CREATE PUMP in the Guavus s-Server Streaming SQL Reference Manual.

CREATE OR REPLACE PUMP writer_pump STOPPED AS
INSERT INTO json_writer_stream
(
"id",
"reported_at",
"shift_no",
"trip_no",
"route_variant_id",
"waypoint_id",
"last_known_location_state",
"subroute_id",
"descriptions"
)
SELECT STREAM
"id",
"reported_at",
"shift_no",
"trip_no",
"route_variant_id",
"waypoint_id",
"last_known_location_state",
"subroute_id",
"descriptions"
from "buses_stream";
--this assumes that a stream called "buses_stream" exists in the same schema

Output

[{"id":"5.0115809712E10",
"reported_at":"2014-07-23 20:52:04.527000000",
"shift_no":"NULL",
"trip_no":"653.0",
"route_variant_id":"L38 7",
"waypoint_id":"NULL",
"last_known_location_state":"NULL",
"sub_route":{"id":"123"},
"descriptions": ["string1"]},
{"id":"5.0115854098E10",
"reported_at":"2014-07-23 20:52:05.443000000",
"shift_no":"NULL",
"trip_no":"NULL",
"route_variant_id":"310 7",
"waypoint_id":"NULL",
"last_known_location_state":"NULL",
"sub_route":{"id":"NULL"},
"descriptions": []},
{"id":"3.46866848031E11",
"reported_at":"2014-07-23 20:52:07.713000000",
"shift_no":"1016.0",
"trip_no":"NULL",
"route_variant_id":"806 160",
"waypoint_id":"1.5588646E7",
"last_known_location_state":"NULL",
"descriptions": ["string1", "string2"]}]

Schema

{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "reported_at": {
      "type": "string"
    },
    "shift_no": {
      "type": "string"
    },
    "trip_no": {
      "type": "string"
    },
    "route_variant_id": {
      "type": "string"
    },
    "waypoint_id": {
      "type": "string"
    },
    "last_known_location_state": {
      "type": "string"
    },
    "sub_route": {
      "type": "object",
      "properties": {
        "id": {
          "type": "string"
        }
      },
      "required": [
        "id"
      ],
	  "additionalProperties" : false
    },
    "descriptions" : {
      "type": "array",
       "items": [
            {
              "type": ["string","null"]
            }
      ]
    }
  },
  "required": [
    "id",
    "reported_at",
    "shift_no",
    "trip_no",
    "route_variant_id",
    "waypoint_id",
    "last_known_location_state",
    "sub_route",
    "descriptions"
  ],
  "additionalProperties" : false
}

Foreign Stream Options for Formatting JSON Data

Option Definition
FORMATTER This needs to be JSON.
CHARACTER_ENCODING Character set for data.
FORMATTER_INCLUDE_ROWTIME Whether or not to include rowtime when writing data. Defaults to 'true'.
NESTED_JSON Whether or not to write data in nested json format. Defaults to 'false'.
SCHEMA_FILE_LOCATION Format and validate the output json based on json schema file.
PATH_COLUMN_NAME Path of the duplicate fields, if any.
STARTING_OUTPUT_ROWTIME If this is set, then all rows with ROWTIME less than the specified value are silently dropped. It is specified using an ISO 8601 format like '2020-02-06T12:34:56.789'.

Using the ECD Agent to Write JSON

You can use the ECD agent to JSON Data to remote locations. See Writing Data to Remote Locations for more details.

The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.

# Column types for the source stream
ROWTYPE=RecordType(INTEGER COL1,TIMESTAMP COL2, INTEGER COL3, BOOLEAN COL4, VARCHAR(32) COL5, VARCHAR(32) COL6, INTEGER COL7)
FORMATTER=JSON
CHARACTER_ENCODING=UTF-8
DIRECTORY=/home/guavus/output
ORIGINAL_FILENAME=stocks-output.json
FILENAME_PREFIX=output-
FILENAME_SUFFIX=.json
FILENAME_DATE_FORMAT=yyyy-MM-dd-HH:mm:ss
FILE_ROTATION_SIZE=20K
FORMATTER_INCLUDE_ROWTIME=true

To invoke the agent, from the directory $SQLSTREAM_HOME/../clienttools/EcdaAgent/ enter the following at the command line

$ ./commondataagent.sh --output --props sample.properties --io file