PPL: how to transform array of objects into single object

Plugins SQL
1

Hello,

I’m trying to extract and merge specific pairs of parent/child spans for OpenTelemetry traces produced by Jaeger.
For the record, here’s what a Jaeger trace document looks like in OpenSearch (I’ve removed some of the items of the tags array for readability):

search source = jaeger-span-2026-10-10
| fields operationName, tags
| head 1

{
  "schema": [
    {
      "name": "operationName",
      "type": "string"
    },
    {
      "name": "tags",
      "type": "array"
    }
  ],
  "datarows": [
    [
      "GET /my/endpoint",
      [
        {
          "type": "string",
          "value": "io.opentelemetry.jetty-12.0",
          "key": "otel.scope.name"
        },
        {
          "type": "string",
          "value": "2.32.0-alpha",
          "key": "otel.scope.version"
        },
        {
          "type": "string",
          "value": "GET",
          "key": "http.request.method"
        },
        {
          "type": "string",
          "value": "format=json",
          "key": "url.query"
        },
        {
          "type": "string",
          "value": "/my/endpoint",
          "key": "url.path"
        },
        {
          "type": "string",
          "value": "my.server.com",
          "key": "server.address"
        },
        {
          "type": "string",
          "value": "193.0.13.50",
          "key": "client.address"
        },
        {
          "type": "string",
          "value": "Java/25.0.4.1",
          "key": "user_agent.original"
        },
        {
          "type": "string",
          "value": "https",
          "key": "url.scheme"
        }
      ]
    ]
  ],
  "total": 1,
  "size": 1
}

My goal is to find pairs of parent/child spans where the child span refers to a specific operation.
With the following join query, I managed to isolate such pairs:

search source = jaeger-span-2026-10-10
| where match(operationName, 'Child operation')
| inner join left = childSpan right = parentSpan on parentSpan.spanID = childSpan.parentSpanID jaeger-span-2026-10-10
| fields parentSpan.tags, childSpan.tags
| head 1

{
  "schema": [
    {
      "name": "parentSpan.tags",
      "type": "array"
    },
    {
      "name": "tags",
      "type": "array"
    }
  ],
  "datarows": [
    [
      [
        {
          "type": "string",
          "value": "io.opentelemetry.jetty-12.0",
          "key": "otel.scope.name"
        },
        {
          "type": "string",
          "value": "2.32.0-alpha",
          "key": "otel.scope.version"
        },
        {
          "type": "string",
          "value": "GET",
          "key": "http.request.method"
        },
        {
          "type": "string",
          "value": "format=json",
          "key": "url.query"
        },
        {
          "type": "string",
          "value": "/my/endpoint",
          "key": "url.path"
        },
        {
          "type": "string",
          "value": "my.server.com",
          "key": "server.address"
        },
        {
          "type": "string",
          "value": "193.0.13.50",
          "key": "client.address"
        },
        {
          "type": "string",
          "value": "Java/25.0.4.1",
          "key": "user_agent.original"
        },
        {
          "type": "string",
          "value": "https",
          "key": "url.scheme"
        }
      ],
      [
        {
          "type": "string",
          "value": "my.app",
          "key": "otel.scope.name"
        },
        {
          "type": "string",
          "value": "XXX-AAA-BBB",
          "key": "api_key"
        }
      ]
    ]
  ],
  "total": 1,
  "size": 1
}

By adding the mvappend step to the pipeline, I get a result which is close to what I want:

...
| eval tags = mvappend(parentSpan.tags, childSpan.tags)
| fields tags
| head 1

{
  "schema": [
    {
      "name": "tags",
      "type": "array"
    }
  ],
  "datarows": [
    [
      [
        {
          "type": "string",
          "value": "io.opentelemetry.jetty-12.0",
          "key": "otel.scope.name"
        },
        {
          "type": "string",
          "value": "2.32.0-alpha",
          "key": "otel.scope.version"
        },
        {
          "type": "string",
          "value": "GET",
          "key": "http.request.method"
        },
        {
          "type": "string",
          "value": "format=json",
          "key": "url.query"
        },
        {
          "type": "string",
          "value": "/my/endpoint",
          "key": "url.path"
        },
        {
          "type": "string",
          "value": "my.server.com",
          "key": "server.address"
        },
        {
          "type": "string",
          "value": "193.0.13.50",
          "key": "client.address"
        },
        {
          "type": "string",
          "value": "Java/25.0.4.1",
          "key": "user_agent.original"
        },
        {
          "type": "string",
          "value": "https",
          "key": "url.scheme"
        },
        {
          "type": "string",
          "value": "my.app",
          "key": "otel.scope.name"
        },
        {
          "type": "string",
          "value": "XXX-AAA-BBB",
          "key": "api_key"
        }
      ]
    ]
  ],
  "total": 1,
  "size": 1
}

Now my question is: how do I get to transform this array of objects into something like the following?

{
  "http.request.method": "GET",
  "url.path": "/my/endpoint",
  "api_key": "XXX-AAA-BBB"
}

Ideally I’d like to keep values with a specific key only.

I’ve spent hours playing with the functions listed on Collection functions | OpenSearch Documentation but couldn’t get it work.

Thanks!

2

@damiano.albani Could you share your OpenSearch version?

3

@damiano.albani Could you also share mappings of jaeger-span-2026-10-10 index?

GET /jaeger-span-2026-10-10/_mapping
4

Hi Pablo,

Thanks for your message.

I’m using OpenSearch 3.9.

As for the mapping, here it is:

{
  "jaeger-span-2026-10-10": {
    "mappings": {
      "dynamic_templates": [
        {
          "span_tags_map": {
            "path_match": "tag.*",
            "mapping": {
              "ignore_above": 256,
              "type": "keyword"
            }
          }
        },
        {
          "process_tags_map": {
            "path_match": "process.tag.*",
            "mapping": {
              "ignore_above": 256,
              "type": "keyword"
            }
          }
        },
        {
          "scope_tags_map": {
            "path_match": "scopeTag.*",
            "mapping": {
              "ignore_above": 256,
              "type": "keyword"
            }
          }
        }
      ],
      "properties": {
        "duration": {
          "type": "long"
        },
        "flags": {
          "type": "integer"
        },
        "logs": {
          "type": "nested",
          "dynamic": "false",
          "properties": {
            "fields": {
              "type": "nested",
              "dynamic": "false",
              "properties": {
                "key": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "type": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "value": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "timestamp": {
              "type": "long"
            }
          }
        },
        "operationName": {
          "type": "keyword",
          "ignore_above": 256
        },
        "parentSpanID": {
          "type": "keyword",
          "ignore_above": 256
        },
        "process": {
          "properties": {
            "serviceName": {
              "type": "keyword",
              "ignore_above": 256
            },
            "tag": {
              "type": "object"
            },
            "tags": {
              "type": "nested",
              "dynamic": "false",
              "properties": {
                "key": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "type": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "value": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "references": {
          "type": "nested",
          "dynamic": "false",
          "properties": {
            "flags": {
              "type": "integer"
            },
            "refType": {
              "type": "keyword",
              "ignore_above": 256
            },
            "spanID": {
              "type": "keyword",
              "ignore_above": 256
            },
            "tags": {
              "type": "nested",
              "dynamic": "false",
              "properties": {
                "key": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "type": {
                  "type": "keyword",
                  "ignore_above": 256
                },
                "value": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "traceID": {
              "type": "keyword",
              "ignore_above": 256
            },
            "traceState": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "scopeTag": {
          "type": "object"
        },
        "scopeTags": {
          "type": "nested",
          "dynamic": "false",
          "properties": {
            "key": {
              "type": "keyword",
              "ignore_above": 256
            },
            "type": {
              "type": "keyword",
              "ignore_above": 256
            },
            "value": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "spanID": {
          "type": "keyword",
          "ignore_above": 256
        },
        "startTime": {
          "type": "long"
        },
        "startTimeMillis": {
          "type": "date",
          "format": "epoch_millis"
        },
        "tag": {
          "properties": {
            "error": {
              "type": "keyword",
              "ignore_above": 256
            },
            "span@kind": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "tags": {
          "type": "nested",
          "dynamic": "false",
          "properties": {
            "key": {
              "type": "keyword",
              "ignore_above": 256
            },
            "type": {
              "type": "keyword",
              "ignore_above": 256
            },
            "value": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "traceID": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    }
  }
}
5

@damiano.albani I did some testing using your index mapping and came up with a workaround. It’s not particularly elegant, but it worked in my OpenSearch 3.9.0 tests.

The challenge was converting the combined array of {key, value} objects into a single JSON object.

I couldn’t find a single PPL function that performs this transformation directly. Instead, the query expands the array, extracts the required key/value pairs, constructs a JSON string, and uses spath to convert it into a structured object (struct).

I’ve also grouped the aggregation by child_id so that tags from different child spans aren’t combined into a single result.

POST /_plugins/_ppl
{
  "query": "source = jaeger-span-2026-10-10 | where match(operationName, 'Child operation') | inner join left = childSpan right = parentSpan on parentSpan.spanID = childSpan.parentSpanID jaeger-span-2026-10-10 | eval child_id = childSpan.spanID, combined_tags = mvappend(parentSpan.tags, childSpan.tags) | expand combined_tags as tag | eval tag_key = cast(tag.key as string), tag_value = cast(tag.value as string) | where tag_key in ('http.request.method', 'url.path', 'api_key') | eval fragment = regexp_replace(json_object(tag_key, tag_value), '^\\\\{|\\\\}$', '') | stats values(fragment) as fragments by child_id | eval result_json = concat('{', mvjoin(fragments, ','), '}') | spath input=result_json output=result | fields child_id, result"
}
6

Hi Pablo,

Marvelous, that was very helpful.
Thanks a lot for the time you spent looking for a solution!

For the record, I slightly adapted the PPL query, which ended up being:

  source = jaeger-span-2026-10-10
  | where operationName = 'Check API Key'
  | inner join left = childSpan right = parentSpan on parentSpan.spanID = childSpan.parentSpanID jaeger-span-2026-10-10
  | eval child_span_id = childSpan.spanID, combined_tags = mvappend(parentSpan.tags, childSpan.tags)
  | expand combined_tags as expanded_tags
  | eval tag_key = cast(expanded_tags.key as string), tag_value = cast(expanded_tags.value as string)
  | where tag_key in ('http.request.method', 'url.path', 'api_key')
  | eval fragment = regexp_replace(json_object(replace(tag_key, '\.', '@'), tag_value), '^\\{|\\}$', '')
  | stats values(fragment) as fragments by child_span_id
  | eval result_json = concat('{', mvjoin(fragments, ','), '}')
  | spath input=result_json output=http_method path='http@request@method'
  | spath input=result_json output=url_path path='url@path'
  | spath input=result_json output=api_key path='api_key'
  | fields http_method, url_path, api_key

Which produced a result like:

{
  "schema": [
    {
      "name": "http_method",
      "type": "string"
    },
    {
      "name": "url_path",
      "type": "string"
    },
    {
      "name": "api_key",
      "type": "string"
    }
  ],
  "datarows": [
    [
      "GET",
      "/api/v1/abc",
      "API key #2"
    ],
    [
      "GET",
      "/api/v2/xyz",
      "API key #2"
    ],
    ...

Thanks again, as I would never have come up with the full PPL query!