Copy page

Transformations

Working with Nested JSON

Use these when your raw data is deeply nested JSON and you need to flatten or navigate it before applying other transformations.

---

Select — SELECT

Zoom into a specific path in your JSON and use that as the starting point for everything that follows.

Use this when: an API response wraps the actual data inside nested objects like data.results and you want to work with just the inner array.

Before (full API response)

Code
{
  "status": "ok",
  "data": {
    "results": [
      { "device": "AC-01", "kwh": 1.2 },
      { "device": "AC-02", "kwh": 1.7 }
    ]
  }
}

After — selecting data.results as the new root

Code
[
  { "device": "AC-01", "kwh": 1.2 },
  { "device": "AC-02", "kwh": 1.7 }
]

Code
{
  "type": "SELECT",
  "config": {
    "path": "data.results"
  }
}

---

Extract — EXTRACT

Pull specific values out of a nested structure and combine them into a flat object.

Use this when: you need several values from different parts of a nested JSON response and want them in one flat record.

Before

Code
{
  "station": "Changi",
  "readings": {
    "temperature": { "high": 34, "low": 26 },
    "humidity": 82
  }
}

After — extracting temperature high, low, and humidity as top-level fields

Code
{
  "temp_high": 34,
  "temp_low": 26,
  "humidity": 82
}

Code
{
  "type": "EXTRACT",
  "config": {
    "temp_high": "readings.temperature.high",
    "temp_low":  "readings.temperature.low",
    "humidity":  "readings.humidity"
  }
}

---

Fields — FIELDS

Pick or omit a set of top-level keys from a JSON object or array of objects.

Use this when: your data is plain JSON (not yet in a table) and you want to keep or remove certain fields before any table-based transformations run.

Before

Code
[
  { "device_id": "AC-01", "kwh": 1.2, "_id": "abc", "__v": 0 },
  { "device_id": "AC-02", "kwh": 1.7, "_id": "def", "__v": 0 }
]

After — picking only device_id and kwh

Code
[
  { "device_id": "AC-01", "kwh": 1.2 },
  { "device_id": "AC-02", "kwh": 1.7 }
]

Code
{
  "type": "FIELDS",
  "config": {
    "mode": "pick",
    "fields": ["device_id", "kwh"]
  }
}

Set "mode": "omit" to remove the listed fields instead of picking them.