jqlang

Yet another jqlang cheat sheet.

Introduction

jq is like sed for JSON data - you can use it to slice and filter and map and transform structured data with the same ease that sed, awk, grep and friends let you play with text. To learn fundamentals about jqlang, the expression language provided by jq, please refer to the jq tutorial and the jq manual documentation, and do a few orientation flights on the jq playground.

The jqlang expressions on this page focus on common usage with Tikray, specifically its jq’s update operator |=, as well as jq’s built-in functions and Tikray’s jqlang standard library, including a few support functions you may find useful. Contributions to them are always welcome.

Structure

Structural reshaping of nested data before actually processing records is needed in many cases, for example by applying destructuring, flattening, un-nesting, or other unwrapping operations to top-level elements or substructures. Editing the structure mostly applies to container elements object and array.

Unwrap and flatten

It is typical for HTTP JSON API responses to not start directly with a collection of data items, because a typical response also includes other metadata. In this spirit, when connecting pipeline elements of JSON processors, input data mostly needs to be edited into a variant suitable for storing into a database, likely also transitioning from a top-level object to a top-level list.

• Unwrap the actual collection which is nested within the top-level records element.

• Flatten the element nested-list which contains nested lists.

expression: .records[] | ."nested-list" |= flatten

Example

Input data

{
  "message-source": "community",
  "message-type": "mixed-pickles",
  "records": [
    {"nested-list": [{"foo": 1}, [{"foo": 2}, {"foo": 3}]]}
  ]
}

Output data

[
  {"nested-list": [{"foo": 1}, {"foo": 2}, {"foo": 3}]}
]

Transformation definition

# Tikray collection-level transformation definition.
# Includes a Moksha/jq transformation rule for unwrapping and flattening.
# https://tikray.readthedocs.io/
---
meta:
  type: tikray-collection
  version: 1
pre:
  rules:
  - expression: .records[] | ."nested-list" |= flatten
    type: jq

Select elements

Select object attributes by path, also multiple ones at once. Selecting elements of input documents is sometimes also called “pick fields” or “include columns”.

expression: .[] |= pick(.meta, .data.temp)

Example

Input Data

[{
  "meta": {"id": "Hotzenplotz", "timestamp": 123456789},
  "data": {"temp": 42.42, "hum": 84}
}]

Output Data

[{
  "meta": {"id": "Hotzenplotz", "timestamp": 123456789},
  "data": {"temp": 42.42}
}]

Transformation definition

# Tikray collection-level transformation definition.
# Includes a Moksha/jq transformation rule for including elements.
# https://tikray.readthedocs.io/
---
meta:
  type: tikray-collection
  version: 1
pre:
  rules:
  - expression: .[] |= pick(.meta, .data.temp)
    type: jq

Drop elements

Drop object attributes by path, also multiple ones at once. Dropping elements of input documents is sometimes also called “ignore fields” or “exclude columns”.

expression: .[] |= del(.meta.timestamp, .data.hum)

Example

Input Data

[{
  "meta": {"id": "Hotzenplotz", "timestamp": 123456789},
  "data": {"temp": 42.42, "hum": 84}
}]

Output Data

[{
  "meta": {"id": "Hotzenplotz"},
  "data": {"temp": 42.42}
}]

Transformation definition

# Tikray collection-level transformation definition.
# Includes a Moksha/jq transformation rule for excluding elements.
# https://tikray.readthedocs.io/
---
meta:
  type: tikray-collection
  version: 1
pre:
  rules:
  - expression: .[] |= del(.meta.timestamp, .data.hum)
    type: jq

Drop attribute from all objects in array, where in some documents, the array may not exist, or it might not be an array.

expression: .[] |= del(.data.array[]?.hum)

Example

Input Data

[
  {"data": {"array": [
    {"temp": 42.42, "hum": 84},
    {"temp": 42.42, "hum": 84},
    {"temp": 42.42}
  ]}},
  {"data": {"array": 42}},
  {"data": {}},
  {"meta": {"version": 1}}
]

Output Data

[
  {"data": {"array": [
    {"temp": 42.42},
    {"temp": 42.42},
    {"temp": 42.42}
  ]}},
  {"data": {"array": 42}},
  {"data": {}},
  {"meta": {"version": 1}}
]

Drop array elements by index.

expression: .[] |= del(.data.[1])

Example

Input Data

[{"data": [1, {"hum": 84}, 2]}]

Output Data

[{"data": [1, 2]}]

Rename elements

Todo

Rename input elements, on any nesting level.

Values

Transform actual field values, by applying converter functions, casting types, and filtering on value content.

Convert

Converting values is one of the most prominent tasks when transforming data.

Arithmetic

Arithmetic operations like .data.temp /= 100 can easily apply value scaling anywhere in the input document. See also jq’s arithmetic-update operator.

Update value of deeply nested attribute if it exists.

expression: .[] |= if .data.temp then .data.temp /= 100 end

Example

Input Data

[
  {"data": {"temp": 4242}},
  {"meta": {"version": 1}}
]

Output Data

[
  {"data": {"temp": 42.42}},
  {"meta": {"version": 1}}
]

Update value of deeply nested attribute within an array if it exists.

expression: .[] |= if (.data | type == "array") and .data[].temp then .data[].temp /= 100 end

Example

Input Data

[
  {"data": [{"temp": 4242}]},
  {"data": [{"hum": 84}]},
  {"data": null},
  {"data": 42},
  {"meta": {"version": 1}}
]

Output Data

[
  {"data": [{"temp": 42.42}]},
  {"data": [{"hum": 84}]},
  {"data": null},
  {"data": 42},
  {"meta": {"version": 1}}
]

Date / Time

Todo

What can jq do with date / time / timestamp conversions?

Combine

Todo

Combine multiple fields into single ones.

Filter

Todo

Filter by values and value types.

Type cast

Todo

Cast types, i.e. substructure modifications, also see Tikray’s stdlib.