# JSON Job

# Overview

The JSON job provides a way to download JSON files from remote systems or read files from the local file system to convert the files into series, properties, and message commands sent into Axibase Time Series Database.

# Workflow

# File Downloaded via HTTP

1. Download the target JSON file from a remote server.
2. Parse the file into memory as a JSON document.
3. Select objects from the JSON document with the specified JSON Path expression.
4. Build a series, property, or message command from the object fields.
5. Each matched object is translated into a separate set of commands.
6. Repeat Steps 3-5 for each configuration setting/JSON expression.
7. Send commands into Axibase Time Series Database.

# File on Local File System

1. Locate one or multiple files at the specified path on the local file system.
2. Parse the file into memory as a JSON document.
3. Select objects from the JSON document with the specified JSON Path expression.
4. Build a series, property, or message command from the object's fields.
5. Each matched object is translated into a separate set of commands.
6. Repeat Steps 3-5 for each configuration setting/JSON expression.
7. Send commands into Axibase Time Series Database.
8. If the Delete on Upload setting is enabled and commands are accepted by ATSD, delete the source file.
9. Repeat Steps two through eight for each matched file.

# JSON Path

JSONPath is an expression evaluated against the JSON document to select objects or specific fields.

• The expression starts with $, which represents the root object followed by a dot-separated path to matched objects.
• .{cname} denotes a child object of the given object with name cname.
• {arr-name}[*] stands for all elements of the specified array arr-name.

Example:

$.store.book[*]

The expression selects all elements of the book array in the root's child named store.

{
    "store": {
    "book": [{
    "category": "reference",
    "author": "Nigel Rees",
    "title": "Sayings of the Century",
    "price": 8.95
    }, {
    "category": "fiction",
    "author": "Evelyn Waugh",
    "title": "Sword of Honour",
    "price": 12.99
    }]
    }
}

# Download Settings

Name	Description
Name	Name of the configuration.
Protocol	HTTP or File protocol to download JSON files from a remote server or read them from the local file system. File protocol supports wildcards in Path.
HTTP Pool	Pre-defined HTTP connection parameters to limit the number of open connections, to customize timeout settings, and to re-use connections across multiple requests. When HTTP Pool is selected, the Path field must contain a relative URI: [/]path[?query][#fragment]
Path	URI Path to JSON file, for example https://example.org/api/daily-summary.json, or the absolute path to the file or files on the local file system. If the HTTP Pool is enabled, the path must be relative, for example /api/daily-summary.json. Otherwise, the Path must be a full URI including protocol, host, port, and the path. The Path supports the following placeholders: - ${ITEM}: current element in the Item List. - ${TIME()}: text output of the TIME function. - ${DATE_ITEM()}: text output of the DATE_ITEM function. If ${DATE_ITEM()} is present in the Path, the job executes as many queries as there are elements returned by the ${DATE_ITEM()} function, substituting the ${DATE_ITEM()} placeholder with the element value for each request. The Path can include either the ${DATE_ITEM()} or ${ITEM} function, but not both.
Format	JSON or JSON Lines. If the JSON Lines format is selected, the database adds the input lines contained in the file to a parent array object and processes the lines as a single JSON document.
Delete Files on Upload	Applies to FILE protocol. Delete source files parsed into at least one command and successfully sent to the database.
Ignore Unchanged Files	Prevents unchanged files or HTTP entities from being repeatedly processed. When enabled, Collector compares the last modified time of the file (FILE) or Last-Modified header/MD5 hashcode (HTTP, HTTP_POOL) with the previously stored value and ignores the value if there are no changes. In the case of HTTP and HTTP_POOL protocols, Collector checks the Last-Modified response header. If the header is present and the value has not changed since the last execution, the response content is not downloaded.
Ignore Invalid Commands	If enabled, invalid commands are ignored.
Item List	A collection of elements to execute multiple requests for different JSON files in a loop. The current element in the loop can be accessed with the ${ITEM} placeholder, which can be embedded into the Path and Default Entity fields. When Item List is selected and ${ITEM} is present in the Path, the job executes as many queries as there are elements in the list, substituting ${ITEM} with the element value for each request. The placeholder ${ITEM} supports standard functions and column function.
Replacement Table	A set of mappings for converting entity names retrieved from the JSON document into entity names to be stored in the database.
Enable Stream Reader	If enabled, the job processes items sequentially.
HTTP Method	HTTP Method executed: GET or POST. The POST method provides a way to specify request parameters in payload.
Payload	POST request payload.
HTTP Headers	Specify request headers.
Enable Web Driver	When enabled, executes the Driver Script.
Driver Script*	Downloads the file by executing a set of pre-recorded browser actions such as opening a page and clicking on a button to export a file. The script is recorded in Selenium IDE and exported into Java format.
Driver Timeout, seconds*	Maximum time allowed for the Driver Script to run before driver stops process.
Driver File Encoding*	File Encoding to use when saving a file downloaded with Driver Script.

# Conversion Settings

# JSON Fields

Name	Description
JSON Path	JSON Path expression to match an object or a list of objects in the JSON document. The default path is $, which stands for the root object. JSON Path supports the following placeholders: - ${ITEM}: current element in the Item List. - ${TIME()}: text output of the TIME function. - ${DATE_ITEM()}: text output of the DATE_ITEM function. If ${DATE_ITEM()} is present in the JSON Path, the JSON Path expression returns a combined list of objects that matched any of the elements returned by ${DATE_ITEM()} function.
Traversal Depth	Maximum traversal limit measured as the difference between the matched object and nested objects. When Depth is set to one, Collector includes only direct fields of the matched object. If Depth is set to zero or a negative number, all nested objects are traversed and included into the commands by Collector.
Renamed Fields	Pairs of oldname=newname mappings, one per line, to rename fields in the matched object.
Custom Tags	Additional series, property, and message tags. Supported placeholders: - ${HOST} - Hostname from which the JSON document is loaded. - ${PARENT(n)} - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}. - ${field_name} - Value of the specified filed in the matched object.

# Entity Fields

Name	Description
Default Entity	Entity used in all commands (example). This field supports the following options: - Text value - ${HOST} placeholder - Hostname from which the JSON document is loaded. - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} placeholder - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}.
Entity Field	Value used as the entity in all commands (example). This field supports the following options: - Name of the field containing entity in the matched object. - JSON Path.
Entity Prefix	Text added to entity name extracted retrieved from the specified field (example). For example, if the Entity Prefix is set to custom., and the field value is my-host, the resulting entity name is custom.my-host.

# Series Fields

Name	Description
Metric Prefix	Text added to metric name. For example, if Metric Prefix is set to custom. and the metric name is cpu_busy, the resulting metric name is custom.cpu_busy.
Included Fields	Specify fields that must be included into the Series command (example). If you leave the field empty, all values are included in the command. You can use the . symbol for nested fields. The wildcard * is supported.
Excluded Fields	Specify fields that must be excluded from the Series command (example). You can use the . symbol for nested fields. The wildcard * is supported.
Metric Name Field	Metric name extracted from the given field in the matched object (example). This field supports additional option: - ${ITEM} = Current element in the Item List.
Metric Value Field	Metric value extracted from the given field in the matched object (example).

# Property Fields

Name	Description
Property Default Type	Property type that used as a default type for all properties (example). This field supports the following options: - Text value - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} placeholder - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}.
Property Type Field	Field with value that used as property type (example). This field supports the following options: - Name of the field containing property type in the matched object. - JSON Path.
Property Key Fields	Fields that must be included into the Property command value collection (example).
Property Value Fields	Fields that must be loaded to a collection as properties (example).

# Time Fields

Name	Description
Time Default	Specify time value for all commands (example). This field supports the following options: - ${TIME()} - text output of the TIME function. - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}. Field supports additional placeholders, if the FILE protocol is selected: - ${FILE} - Name of the file. - ${DIRECTORY} - Parent directory of the file. - ${PATH} - Full path to the file.
Time Field	Field with values that specify time for all commands (example). This field supports the following options: - Name of the field containing date in the matched object - JSON Path
Time Format	Date format applied when parsing time value (example).
Time Zone	Time zone can be optionally applied if the extracted date is in local time, otherwise the local Collector time zone is in effect (example).
Minimum Time	Calendar expression to specify minimum time for commands. Commands with timestamp earlier than specified are ignored.

# Message Fields

Name	Description
Message Default Type	Message type that used as the default type for all messages (example). This field supports the following options: - Text value. - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} placeholder - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}. Field supports additional placeholders, if the FILE protocol is selected: - ${FILE} - Name of the file. - ${DIRECTORY} - Parent directory of the file. - ${PATH} - Full path to the file.
Message Type Field	Field with value used as message type (example). This field supports the following options: - Name of the field containing message type in the matched object. - JSON Path.
Message Default Type	Message source used as the default source for all messages (example). This field supports the following options: - Text value. - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} placeholder - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}. Field supports additional placeholders, if the FILE protocol is selected: - ${FILE} - Name of the file. - ${DIRECTORY} - Parent directory of file. - ${PATH} - Full path to file.
Message Type Field	Field with value used as message source (example). This field supports the following options: - Name of the field containing message source in the matched object. - JSON Path.
Message Tag Fields	Message tags, included as tags into the message command (example).
Message Default	Message value used as a default text for all messages (example). This field supports the following options: - Text value. - ${ITEM} placeholder - Current element in the Item List. - ${PARENT(n)} placeholder - Name of the Nth parent of the matched object. {PARENT} is a shortcut for ${PARENT(1)}. Field supports additional placeholders, if the FILE protocol is selected: - ${FILE} - Name of the file. - ${DIRECTORY} - Parent directory of file. - ${PATH} - Full path to file.
Message Field	Field with value used as message text (example). This field supports the following options: - Name of the field containing message source in the matched object. - JSON Path.

# Placeholders

Name	Description
${ITEM}	Current element in the Item List.
${TIME()}	Text output of the TIME function.
${DATE_ITEM()}	Current element in the Date Item List.
${HOST}	Host name.
${PARENT}	Shortcut for ${PARENT(1)}
${PARENT(n)}	Parent name from JSON Path of the matched object.
${PATH}	Absolute path of file.
${FILE}	Name of file.
${DIRECTORY}	Parent directory of file.

# Examples

• Australia Bureau of Meteorology Weather
• JSON Lines
• GitHub Daily Summary
• nginx Status
• BLS
• Fields with non alphanumeric characters

# Additional Examples

• Json Fields
  • Custom Tags
• Entity Fields
  • Default Entity
  • Entity Field
• Series Fields
  • Metric Prefix
  • Included Fields
  • Excluded Fields
  • Metric Name and Value Fields
• Property Fields
  • Property Default Type
  • Property Type Field
  • Property Key and Value Fields
• Time Fields
  • Time Default
  • Time Field
• Message Fields
  • Message Defaults
  • Message Fields

# JSON Fields Examples

# Custom Tags

JSON:

{
  "upstreams": {
    "demo-backend": {
      "peers": [
        {
          "active": 0,
          "responses": {
            "1xx": 0,
            "total": 0
          },
          "id": 1,
          "server": "198.51.100.1:1234"
        }
      ]
    }
  }
}

Field Name	Field Value
Default Entity	tst
JSON Path	$.upstreams.demo-backend.peers.*
Depth	2
Custom Tags	server=${server}<br/>name=${PARENT(2)}<br/>type=${PARENT(3)}.${PARENT}

Result:

series e:tst d:2016-07-07T15:22:59.593Z t:name=demo-backend t:server=198.51.100.1:1234 t:type=upstreams.peers m:id=1 m:active=0 m:responses.total=0 m:responses.1xx=0

# Entity Fields Examples

# Default Entity

JSON:

{
  "upstreams": {
    "demo-backend": {
      "peers": [
        {
          "active": 0,
          "responses": {
            "1xx": 0,
            "total": 0
          },
          "id": 1,
          "server": "198.51.100.1:1234"
        }
      ]
    }
  }
}

• Default Entity contains placeholder ${HOST}:

  Field Name	Field Value
  Path	http://example.org
  Default Entity	${HOST}
  JSON Path	$.upstreams.demo-backend.peers.*
  Depth	2

  Result:

  series e:example.org d:2016-07-07T15:19:01.365Z m:id=1 m:active=0 m:responses.total=0 m:responses.1xx=0
  

• Default Entity contains placeholder ${PARENT(n)}:

  Field Name	Field Value
  Path	http://example.org
  Default Entity	${PARENT(2)}
  JSON Path	$.upstreams.demo-backend.peers.*
  Depth	2

  Result:

  series e:demo-backend d:2016-07-07T15:19:34.924Z m:id=1 m:active=0 m:responses.total=0 m:responses.1xx=0
  

• Default Entity contains text:

  Field Name	Field Value
  Path	http://example.org
  Default Entity	tst
  JSON Path	$.upstreams.demo-backend.peers.*
  Depth	2

  Result:

  series e:tst d:2016-07-07T15:19:34.924Z m:id=1 m:active=0 m:responses.total=0 m:responses.1xx=0
  

# Entity Field

JSON:

{
  "upstreams": {
    "demo-backend": {
      "peers": [
        {
          "active": 0,
          "responses": {
            "1xx": 0,
            "total": 0
          },
          "id": 1,
          "server": "198.51.100.1:1234",
          "type": "peer"
        }
      ]
    }
  }
}

Field Name	Field Value
Path	http://example.org
Entity Field	type
Entity Prefix	tst.
JSON Path	$.upstreams.demo-backend.peers.*
Depth	1

Result:

series e:tst.peer d:2016-07-06T08:14:42.540Z m:id=1 m:active=0

# Series Fields Examples

# Metric Prefix

JSON:

{
  "has_more": false,
  "items": [
    {
      "count": 878240,
      "name": "java"
    }
  ],
  "quota_max": 10000,
  "quota_remaining": 9923
}

Field Name	Field Value
Default Entity	tst
JSON Path	$
Depth	0
Metric Prefix	mp.

Result:

series e:tst d:2016-07-06T07:27:48.184Z m:mp.quota_max=10000 m:mp.items.0.count=878240 m:mp.quota_remaining=9923

# Included Fields

JSON:

{
  "has_more": false,
  "items": [
    {
      "count": 878240,
      "name": "java"
    }
  ],
  "quota_max": 10000,
  "quota_remaining": 9923
}

Field Name	Field Value
Default Entity	tst
JSON Path	$
Depth	1
Included Fields	quota_remaining,has_more

Result:

series e:tst d:2016-07-06T07:14:42.540Z t:has_more=false m:quota_remaining=9923

# Excluded Fields

JSON:

{
  "has_more": false,
  "items": [
    {
      "count": 878240,
      "name": "java"
    }
  ],
  "quota_max": 10000,
  "quota_remaining": 9923
}

Field Name	Field Value
Default Entity	tst
JSON Path	$
Depth	0
Excluded Fields	quota_remaining

Result:

series e:tst d:2016-07-06T07:27:48.184Z m:quota_max=10000 m:items.0.count=878240

# Metric Name and Value Fields

JSON Lines:

[{"data":[{"pitagname":"metric1","pitagvalue":350.0,"timestamp":"2016-07-01T15:59:07.6382972+05:30"}]}]
[{"data":[{"pitagname":"metric2","pitagvalue":250.0,"timestamp":"2016-07-01T15:58:07.6382972+05:30"}]}]

Field Name	Field Value
Default Entity	tst
JSON Path	$..data.*
Depth	1
Time Field	timestamp
Time Format	yyyy-MM-dd'T'HH:mm:ss.SSSSSSSZ
Excluded Fields	pitagvalue
Metric Name Field	pitagname
Metric Value Field	pitagvalue

Result:

series e:tst d:2016-07-01T10:29:07.638Z m:metric1=350
series e:tst d:2016-07-01T10:28:07.638Z m:metric2=250

# Property Fields Examples

# Property Default Type

JSON:

{
  "upstreams": {
    "demo-backend": {
      "peers": [
        {
          "active": 0,
          "responses": {
            "1xx": 0,
            "total": 0
          },
          "id": 1,
          "server": "198.51.100.1:1234"
        }
      ]
    }
  }
}

Field Name	Field Value
Default Entity	tst
JSON Path	$.upstreams..peers.
Depth	1
Property Default Type	${PARENT(3)}.${PARENT}

Result:

property t:upstreams.peers e:tst d:2016-07-06T07:42:46.824Z v:state=up v:server=198.51.100.1:15431 v:backup=false

# Property Type Field

JSON:

{
  "has_more": false,
  "items": [
    {
      "count": 878240,
      "name": "java"
    }
  ],
  "quota_max": 10000,
  "quota_remaining": 9923,
  "type": "type1"
}

Field Name	Field Value
Default Entity	tst
JSON Path	$
Depth	0
Property Type Field	type

Result:

property t:type1 e:tst d:2016-07-06T07:46:58.874Z v:type=type1 v:items.0.name=java v:has_more=false

# Property Key and Value Fields

JSON:

{
  "has_more": false,
  "items": [
    {
      "count": 878240,
      "name": "java"
    }
  ],
  "quota_max": 10000,
  "quota_remaining": 9923,
  "type": "type1"
}

Field Name	Field Value
Default Entity	tst
JSON Path	$
Depth	0
Property Type Field	type
Property Key Fields	name
Property Value Fields	quota_max,has_more

Result:

property t:type1 e:tst d:2016-07-06T07:46:58.874Z k:name=java v:quota_max=100000 v:has_more=false

# Time Fields Examples

# Time Field

JSON:

{
  "data": [
    {
      "date": "2016-01-01",
      "ok": 10,
      "fail": 2
    },
    {
      "date": "2016-01-02",
      "ok": 15,
      "fail": 2
    }
  ]
}

Field Name	Field Value
Default Entity	tst
JSON Path	$.data.*
Time Field	date
Time Format	yyyy-MM-dd
Time Zone	UTC

Result:

series e:tst d:2016-01-01T00:00:00.000Z m:fail=2 m:ok=10
series e:tst d:2016-01-02T00:00:00.000Z m:fail=2 m:ok=15

# Time Default

JSON:

{
  "2016-01-01": {
    "ok": 10,
    "fail": 2
  },
  "2016-01-02": {
    "ok": 15,
    "fail": 2
  }
}

Field Name	Field Value
Default Entity	tst
JSON Path	$.*
Time Default	${PARENT}
Time Format	yyyy-MM-dd
Time Zone	UTC

Result:

series e:tst d:2016-01-01T00:00:00.000Z m:fail=2 m:ok=10
series e:tst d:2016-01-02T00:00:00.000Z m:fail=2 m:ok=15

# Minimum Time

JSON:

{

    "count": 1848,
    "uniques": 123,
    "views": [
        {
            "timestamp": "2018-05-22T00:00:00Z",
            "count": 177,
            "uniques": 20
        },
        {
            "timestamp": "2018-05-23T00:00:00Z",
            "count": 269,
            "uniques": 15
        },
        {
            "timestamp": "2018-05-24T00:00:00Z",
            "count": 128,
            "uniques": 18
        }
    ]
}

Field Name	Field Value
Default Entity	${ITEM}
JSON Path	$.views
Time Field	timestamp
Time Format	yyyy-MM-dd'T'HH:mm:ssZ
Minimum Time	NOW-1*DAY
Metric Prefix	repo.traffic.

If current time is 2018-05-23T17:00:00Z, the job adds these commands to ATSD.

Result:

series e:axibase/atsd d:2018-05-23T00:00:00.000Z m:repo.traffic.uniques=6 m:repo.traffic.count=15
series e:axibase/atsd d:2018-05-24T00:00:00.000Z m:repo.traffic.uniques=5 m:repo.traffic.count=43

# Message Fields Examples

# Message Defaults

• Defaults contain placeholders:

  JSON:

  {
    "upstreams": {
      "demo-backend": {
        "peers": [
          {
            "active": 0,
            "responses": {
              "1xx": 0,
              "total": 0
            },
            "id": 1,
            "server": "198.51.100.1:1234"
          }
        ]
      }
    }
  }
  

  Field Name	Field Value
  Default Entity	tst
  JSON Path	$.upstreams..peers.
  Depth	1
  Message Default Type	${PARENT(3)}.${PARENT}
  Message Default Source	${PARENT(2)}
  Message Default	${PARENT(1)}

  Result:

  message e:tst d:2016-07-06T08:19:30.563Z t:source=demo-backend t:type=upstreams.peers m:peers
  

• Defaults contain text:

  JSON:

  {
    "upstreams": {
      "demo-backend": {
        "peers": [
          {
            "active": 0,
            "responses": {
              "1xx": 0,
              "total": 0
            },
            "id": 1,
            "server": "198.51.100.1:1234"
          }
        ]
      }
    }
  }
  

  Field Name	Field Value
  Default Entity	tst
  JSON Path	$.upstreams..peers.
  Depth	1
  Message Default Type	upstream
  Message Default Source	demo
  Message Default	test

  Result:

  message e:tst d:2016-07-06T08:19:30.563Z t:source=demo t:type=upstream m:test
  

# Additional Message Fields

JSON:

{
  "upstreams": {
    "demo-backend": {
      "peers": [
        {
          "active": 0,
          "responses": {
            "1xx": 0,
            "total": 0
          },
          "id": 1,
          "server": "198.51.100.1:1234",
          "type": "peer"
        }
      ]
    }
  }
}

Field Nam	Field Value
Default Entity	tst
JSON Path	$.upstreams.*.peers.*
Depth	1
Message Type Field	type
Message Source Field	server
Message Field
Message Default
Message Tag Fields	id

Result:

message e:tst d:2016-07-06T08:19:30.563Z t:id=1 t:source=198.51.100.1:1234 t:type=peer m:""

# Configuration Example

# Settings Example

# JSON Viewer Example