TL;DR InfluxDB Tech Tips: Handling JSON Objects and Mapping Through Arrays

By Anais Dotis-Georgiou / Product, Use Cases, Developer
May 11, 2022

Navigate to:

There are multiple ways to use Flux to bring in data from a variety of different sources including SQL databases, other InfluxDB Cloud Accounts, Annotated CSV from a URL, and JSON.  However, previously you could only manually construct tables from a JSON object with Flux as described in this first example.

We’ll describe how to work with three examples with increasingly complex JSON types. First we will describe how to work with these JSON types with metasyntactic examples. Then we’ll go over a real-life example using the OpenWeatherMap API.

While working with JSON objects with Flux, it’s important to be aware of the following functions and considerations:

  1. The display() function: Use this function to return the Flux representation of any value as a string. This allows you to visualize your JSON object in the InfluxDB UI. This was released in the latest OSS version as well, so make sure you're up to date.
  2. The array.from() function: Use this function in conjunction with the display() function to view a JSON object as string in the InfluxDB UI. We'll discuss how to use these two functions together in the following sections.
  3. The json.parse() function: Use this function to convert JSON objects into Flux values. See the type conversions here. You'll have to apply this function to the JSON object response from an API call while using http.post().
  4. The time() function: Use this function to convert string timestamps into a time value.
  5. The array.map() function: Use this function to iterate over an array to handle complex JSON types.

An-example-of-parsing-a-JSON-in-the-Data-Explorer-in-the-InfluxDB-UI

An example of parsing a JSON in the Data Explorer in the InfluxDB UI

JSON Example 1

First example: extract top level values from keys.

  • To convert a JSON like: 
    JSON = {"a": 1, 
 "b": 2, 
 "c": 3,
  "time": timestamp_1}
  • Into the following Flux table:
    time a b c
    timestamp_1 1 2 3
  • With the following Flux: 
    jsonData = json.parse(data: JSON)
// uncomment the next line to display the JSON
// array.from(rows: [{data: display(v: jsonData)}])

// convert the timestamp to a time type with the time() function
// use the array.from() function to create a table 
array.from(rows: [{_time: time(v: int(v: jsonData.timestamp_1)) , a: jsonData.a, b: jsonData.b, c: jsonData.c }])
  • Find the code to run this example for yourself here.

JSON Example 2

Second example: map across an array with complex types.

  • To convert a JSON like: 
    JSON = {"a": 1,
 "list": [{"b": 1.1, 
            "time": timestamp_1},
           {"b": 1.2, 
            "time": timestamp_2}
          ]
}
  • Into the following Flux table:
    time a b
    timestamp_1 1 1.1
    timestamp_2 1 1.2
  • With the following Flux: 
    jsonData = json.parse(data: JSON)
// uncomment the next line to display the JSON
// array.from(rows: [{data: display(v: jsonData)}])

// extract the list that we want to map across with array.map 
listData = jsonData.list
// gather any top level values that you want to include in your table as constants
a = jsonData.a
// map across each complex type in the array named "list" 
list = array.map(
arr: listData,
fn: (x) => ({
// convert the timestamp to a time type with the time() function
"_time": time(v::x.time),
"b": x.b,
"city": a
})
)
// finally convert that flattened list into a table with array.from 
array.from(rows: list)
  • Find the code to run this example for yourself here.

JSON Example 3

Third example: Map across an array with complex types that contain a nested array with a complex type.

  • To convert a JSON like: 
    JSON = {"list": [{"b": 1.1, 
            "nestedList": [{"alpha": "foo1", "beta": "bar1"}, {"alpha": "foo2", "beta": "foo2"}],
            "time": 1596632400000000000},
           {"b": 1.2, 
 	       "nestedList": [{"alpha": "foo1", "beta": "bar1"}, {"alpha": "foo2", "beta": "foo2"}],
            "time": 1596632500000000000}
          ]
}
  • Into the following Flux table:
    time alpha b
    timestamp_1 foo1 1.1
    timestamp_2 foo1 1.2
  • With the following Flux: 
    jsonData = json.parse(data: JSON)

// uncomment the next line to display the JSON
// array.from(rows: [{data: display(v: jsonData)}])

// extract the list that we want to map across with array.map 
listData = jsonData.list
// map across each complex type in the array named "list" 
list = listData |> array.map(fn:(x) => {
             pendingList = x.nestedList
             return {
                 id: x.b,
                 _time: time(v: int(v: x.time)),
                 alpha: pendingList[0].alpha,
             }
         })
array.from(rows: list)
  • Find the code to run this example for yourself here.

JSON Example 4

Fourth example: Map across an array with complex types that contain a nested array and evaluate whether values exist or not. This example is similar to example 3 except that it can be used to handle instances where you might have null values.

  • To convert a JSON like: 
    JSON = {"list": [{"b": 1.1, 
            "nestedList": [{"alpha": "foo1", "beta": "bar1"}, {"alpha": "foo2", "beta": "foo2"}],
            "time": 1596632400000000000},
           {"b": 1.2, 
 	       "nestedList": [{"alpha": "null", "beta": "bar1"}, {"alpha": "foo2", "beta": "foo2"}],
            "time": 1596632500000000000}
          ]
}
  • Into the following Flux table:
    time alpha b
    timestamp_1 foo1 1.1
    timestamp_2 foo1 1.2
  • With the following Flux: 
    jsonData = json.parse(data: JSON)

// uncomment the next line to display the JSON
// array.from(rows: [{data: display(v: jsonData)}])

// extract the list that we want to map across with array.map 
listData = jsonData.list
// map across each complex type in the array named "list" 
list = listData |> array.map(fn:(x) => {
             pendingList = x.nestedList
             // filter for the value 
                 |> array.filter(fn: (x) => x.alpha == "foo1")
             // handle null values
             pendingAlpha = if length(arr: pendingList) == 1
                 then
                     pendingList[0].alpha
                 else
                     "foo1"
             return {
                 id: x.b,
                 _time: time(v: int(v: x.time)),
                 alpha: pendingAlpha,
             }
         })
array.from(rows: list)
  • Find the code to run this example for yourself here.

OpenWeatherMap API example: array with complex type

In this example, we’ll learn about how to map across an array with nested JSON objects to  construct a table which makes working with JSON in Flux much easier. Finally, we’ll write the table to InfluxDB with Flux, which consists of the following steps:

  1. Use the http.get() function to submit a GET request to an API and return a JSON object.
  2. Use json.encode() function to convert the object into bytes.
  3. Use the json.parse() function to convert a JSON object or array into a Flux record or array.
  4. Use the array.map() function to map across elements in an array.
  5. Use the array.from() function to construct a table.
  6. Use the to() function to write the data to InfluxDB.

For this section we’ll be using the OpenWeatherMap current weather data API to gather weather data from London on an hourly basis for the last 4 days using the hourly forecast endpoint. The JSON response we’ll return has data for 96 timestamps and looks like:

{
  "cod": "200",
  "message": 0.0179,
  "cnt": 96,
  "list": [
   {
     "dt": 1596632400,
     "main": {
       "temp": 289.16,
       "feels_like": 288.41,
       "temp_min": 289.16,
       "temp_max": 289.16,
       "pressure": 1013,
       "sea_level": 1013,
       "grnd_level": 1010,
       "humidity": 78,
       "temp_kf": 0
     },
     "weather": [
       {
         "id": 804,
         "main": "Clouds",
         "description": "overcast clouds",
         "icon": "04n"
       }
     ],
     "clouds": {
       "all": 100
     },
     "wind": {
       "speed": 2.03,
       "deg": 252,
       "gust":5.46
     },
     "visibility": 10000,
     "pop": 0.04,
     "sys": {
       "pod": "n"
     },
     "dt_txt": "2020-08-05 13:00:00"
   },
   .....
       ],
  "city": {
   "id": 2643743,
   "name": "London",
   "coord": {
     "lat": 51.5085,
     "lon": -0.1258
   },
   "country": "GB",
   "timezone": 0,
   "sunrise": 1568958164,
   "sunset": 1569002733
  }
}

The following Flux code:

  1. Imports the necessary libraries
  2. Returns the request object in and store it the variable resp.
  3. Parses the JSON body of the response and stores the list attribute in the variable nestedListData. This attribute contains a list of arrays with the timestamp and temperature of London.
  4. Maps across the list of objects to extract the datetime and temperature. The temperature exists as a nested array in the main attribute.The Unix datetime value is converted to nanosecond precision by multiplying the value by 1000000000. The time() function is used to convert Unix timestamp to RFC3339 format. This timestamp conversion is performed so that you can visualize your data better.
  5. Constructs a table with the array.from() function and passes in the list of extracted time and temperature values.
  6. Creates _measurement and _field columns with values of  "weather" and "temp", respectively using the set() function. This is preparation for using the to() function that requires that your tables have the following columns:
    • _time
    • _measurement
    • _field
    • _value
  7. Writes the data to the bucket named "bucket" with the to() function.
import "experimental/json"
import "experimental/http"
import "experimental/array"

weatherURL = "https://pro.openweathermap.org/data/2.5/forecast/hourly?lat=35&lon=139&appid=<yourAPIkey>
// http.get() returns the response from the API 
resp = http.get(url: weatherURL)
// json.parse function returns a json object with lists, records, strings, booleans, and floats
jsonData = json.parse(data: resp.body)
// get the list object 
listData = jsonData.list
// extract the city name just once
city = jsonData.city.name
list = array.map(
arr: listData,
fn: (x) => ({
// convert the timestamp to a time type with the time() function
"_time": time(v::x.dt*1000000000),
"_value": x.main.temp,
"city": city
})
)

array.from(rows: list)
|> set(key: "_field", value: "temp")
|> set(key: "_measurement", value: "weather")

|> yield()
|> to(bucket: "bucket")

Final thoughts on handling JSON objects with Flux

I hope this InfluxDB Tech Tips post inspires you to take advantage of Flux to make requests and manipulate JSON responses. Now you can use the array.map() function to map across nested arrays in a JSON to construct rows. As always, you can use the array.from() function to construct a table from those rows. After preparing the output to meet the data requirements of the to() function, we are finally able to write the table. This functionality provides yet another means for data ingestion into InfluxDB. You can execute this logic in a task on a schedule to periodically write data from an HTTP request.

If you are using Flux and need help, please ask for some in our community site or Slack channel. If you’re developing a cool IoT application on top of InfluxDB, we’d love to hear about it, so make sure to share your story! Additionally, please share your thoughts, concerns, or questions in the comments section. We’d love to get your feedback and help you with any problems you run into!