Skip to main content

JSON Data

NQE queries can access data formatted as JSON.

Specifically, JSON data can be embedded within an NQE query using a multi-line JSON literal, as in:

deviceRegions =
"""json
[
    {"device":"core-01", "region":"nyc", "mgmtIp": "112.2.3.1"},
    {"device":"core-02", "region":"nyc", "mgmtIp": "112.2.4.1"},
    {"device":"edge-12", "region":"atl", "mgmtIp": "112.3.3.1"}
]
""";

The JSON literal must start with the characters """json followed by a newline, and is terminated by a newline followed by """. Within this region, any JSON object is allowed.

deviceRegions =
 [ { device: "core-01", region: "nyc", mgmtIp: ipAddress("112.2.3.1") },
   { device: "core-02", region: "nyc", mgmtIp: ipAddress("112.2.4.1") },
   { device: "edge-12", region: "atl", mgmtIp: ipAddress("112.3.3.1") }
 ];

Since the data is represented as NQE data, it can be queried like any other data. For example, the following selects the device name and IP for devices in the "nyc" region:

foreach entry in deviceRegions
where entry.region == "nyc"
select { deviceName: entry.device, ip: entry.mgmtIp }

NQE infers the type for the JSON data. For example, in the above CSV, the device and region columns are treated as String type data, while the mgmtIp column is inferred to be IpAddress type data. More generally, the type inference works as follows:

  • JSON leaf values (string, numerals, boolean values) are given the most specific NQE type that is appropriate. For example, numerals are interpreted as Number type and true and false are interpreted as Boolean type. Strings that are legal IP addresses, subnets or mac addresses are interpreted as IpAddress, IpSubnet and MacAddress types, respectively.
  • JSON objects are interpreted as NQE records, with fields corresponding to the JSON object's properties, where the field types are determined by applying inference on the corresponding property value.
  • JSON arrays are interpreted as NQE lists, where the item type of the list is determined by inferring the types of all the elements in the JSON array, and then unifying them to determine the most-specific common type that can be given to all the JSON array elements. For example, in a JSON array ["abc", "1.2.3.4"], the first element is inferred to be an NQE String, while the second is inferred to be an NQE IpAddress. Since these leaf types are not identical, they are interpreted as String which is the most specific type that represents both elements. If no element type can be determined for the JSON array, either because the types of the elements are incompatible or because there are no elements in the array, then no type is inferred for the array. If the entire JSON value is an array, then this situation is reported as an error in the query.

The unification process applied to determine the common NQE type for a collection of JSON objects works as follows:

  • If all elements being unified have the same type, then that type is the result.
  • If all elements are represented as JSON strings, then the result is String.
  • If all elements are JSON objects, then the result is an NQE record with fields taken from the properties of the element objects. For a given property, if the values for the property among all element objects can be unified, then the property is included in the NQE record with the unified type; otherwise the property is not included in the NQE record. If, for a given property, some element objects do not have the object present, then the corresponding field in the NQE record will be considered optional.
  • If all elements are JSON arrays, then the result is an NQE List type with an item type resulting from unifying the item types of the element JSON arrays. If there is no unifying item type, then there is no unifying type for the collection of arrays.

Note: If the inferred type is not the desired type, then the function extractJson can be used instead. This function allows users to specify the type of data they expect to extract from a string containing a JSON object.