For the complete documentation index, see llms.txt. This page is also available as Markdown.

Complex Type (Array, Map) Handling

Complex type handling in Apache Pinot.

Commonly, ingested data has a complex structure. For example, Avro schemas have records and arrays while JSON supports objects and arrays.

Apache Pinot's data model supports primitive data types (including int, long, float, double, BigDecimal, string, bytes), and limited multi-value types, such as an array of primitive types. Simple data types allow Pinot to build fast indexing structures for good query performance, but does require some handling of the complex structures.

There are three options for complex type handling:

  • Convert the complex-type data into a JSON string and then build a JSON index.

  • Use OPEN_STRUCT when an object column should stay in one field, but frequently queried keys need columnar storage and secondary indexes.

  • Use the built-in complex-type handling rules in the ingestion configuration.

On this page, we'll show how to handle these complex-type structures with each of these three approaches. We will process some example data, consisting of the field group from the Meetup events Quickstart example.

This object has two child fields and the child group is a nested array with elements of object type.

Example JSON data

JSON indexing

Apache Pinot provides a powerful JSON index to accelerate the value lookup and filtering for the column. To convert an object group with complex type to JSON, add the following to your table configuration.

The config transformConfigs transforms the object group to a JSON string group_json, which then creates the JSON indexing with configuration jsonIndexColumns. To read the full spec, see meetupRsvpJson_realtime_table_config.json.

Also, note that group is a reserved keyword in SQL and therefore needs to be quoted in transformFunction.

The columnName can't use the same name as any of the fields in the source JSON data, for example, if our source data contains the field group and we want to transform the data in that field before persisting it, the destination column name would need to be something different, like group_json.

Note that you do not need to worry about the maxLength of the field group_json on the schema, because "JSON" data type does not have a maxLength and will not be truncated. This is true even though "JSON" is stored as a string internally.

The schema will look like this:

For the full specification, see json_meetupRsvp_schema.json.

With this, you can start to query the nested fields under group. For more details about the supported JSON function, see guide).

OPEN_STRUCT storage and per-key indexes

Use OPEN_STRUCT when your source field is an object or map whose key set evolves over time, but you still want Pinot to store the most important keys as standard columns.

Pinot stores an OPEN_STRUCT column in two tiers:

  • Dense keys become materialized child columns named <column>$<key>.

  • Remaining keys are packed into one sparse JSON column named <column>$__sparse__.

Pinot decides which keys are dense in this order:

  • Keys listed in denseKeys are always materialized.

  • Other keys are materialized when their fill rate is at least denseKeyMinFillRate (default 0.5).

  • If more keys qualify than maxDenseKeys allows, Pinot keeps the highest-fill-rate keys as dense and writes the rest to the sparse JSON column.

Dense keys reuse Pinot's standard column infrastructure, so each materialized key gets a forward index and can also use vetted per-key settings for dictionary, inverted, range, and bloom-filter behavior through valueFieldConfigs. If you do not configure a dense key explicitly, Pinot defaults to dictionary encoding plus an inverted index for that key.

For a materialized key configured with "encodingType": "RAW", add "dictionary": {} under its indexes to enable a dictionary without also enabling an inverted index. This supports a range index on that key while keeping the per-key config consistent with ordinary columns.

After changing a materialized key's index settings, reload the affected segments to apply them, as you would for an ordinary column. Reloading does not change which keys are dense: that choice is made when each segment is built.

Define the schema

Declare the object column as OPEN_STRUCT. childFieldSpecs is optional, but it is useful when some keys should always keep a specific type:

Configure dense keys and per-key indexes

Add an open_struct entry to the field's indexes object in fieldConfigList:

Use ignoredKeys for object keys that Pinot should discard during ingestion. An ignored key is not materialized as a dense child column, is not written to the sparse column, and cannot be queried. Ignored keys must not also appear in denseKeys, valueFieldConfigs, or the schema's childFieldSpecs.

To query fields inside nested objects, set maxNestedKeyDepth in open_struct. The default 1 keeps each nested object as one value; 2 also exposes paths such as device.os from {"device":{"os":"ios"}}. Query that path as attributes['device.os']. The container key attributes['device'] still returns the whole object as JSON text. Nested container keys are not automatically selected as dense keys unless listed in denseKeys; the depth limit changes which paths are addressable, not whether the original object is retained.

Query OPEN_STRUCT keys

Access a key with the item operator. The same syntax works in projections, filters, and aggregations:

For a materialized key, Pinot reads the generated child column and can use its dictionary, inverted, range, or other configured index. Per-key index filtering supports equality and inequality, IN, NOT IN, ranges, IS NULL, and IS NOT NULL. EXPLAIN PLAN reports delegateTo:per_key_index when the filter uses this path.

Keys stored in the shared sparse column are also available through the item operator. Pinot exposes each sparse key as a virtual typed data source, so projections, filters, grouping, and aggregations use the same SQL syntax as dense keys. Sparse keys use scan-based execution by default. Set sparseJsonIndex to true to build a JSON index over the sparse column; Pinot can use it for compatible string-key equality and IN predicates, while other predicates continue to scan the virtual data source.

You can instead configure the sparse blob with sparseFieldConfig inside open_struct. For example, "sparseFieldConfig": {"name": "attributes$__sparse__", "indexes": {"json": {}}} builds the same JSON index as "sparseJsonIndex": true. The blob remains raw-encoded even if its field config requests a dictionary. See the table reference for the complete config surface.

Keys with array values can be stored and queried as multi-value keys. A declared child field controls its type and single- or multi-value shape; for an undeclared key, Pinot infers the shape and element type from ingested values. You can also select the whole OPEN_STRUCT column (including through SELECT *); Pinot reconstructs it as a JSON document from dense and sparse keys.

New segments record the inferred type of undeclared sparse keys, so a key keeps its type whether it is dense or sparse in that segment. Older segments without this type metadata continue to expose undeclared sparse keys as STRING; rebuild those segments if you need their inferred types preserved.

A key that is absent from a document returns its type's default null value and is marked null when null handling is enabled. The same rule applies when a key is absent from an entire segment: Pinot uses the key's declared type and defaultNullValue from childFieldSpecs, or a single-value STRING field with the standard default when the key is undeclared. With null handling enabled, the lookup returns NULL, IS NULL matches every document, and value predicates do not match nulls. With null handling disabled, projections and predicates see the configured or type-specific default value instead. For example, if an absent declared STRING key has "defaultNullValue": "N/A", attributes['key'] = 'N/A' matches every document in that segment.

Notes:

  • OPEN_STRUCT is a field-level index for single-value OPEN_STRUCT columns.

  • Pinot can still ingest keys that are not listed in childFieldSpecs; it infers a stored type from observed values when possible.

  • Changes to ignoredKeys affect only newly ingested data. Existing sealed segments retain values ingested under the earlier configuration.

  • When any schema field uses OPEN_STRUCT, $ becomes a reserved character in schema column names because Pinot uses it in generated child-column names.

  • Use the schema reference for the exact schema JSON and the table reference for the full open_struct config surface.

Flatten and unnest with ingestion configurations

Though JSON indexing is a handy way to process the complex types, there are some limitations:

  • It’s not performant to group by or order by a JSON field, because JSON_EXTRACT_SCALAR is needed to extract the values in the GROUP BY and ORDER BY clauses, which invokes the function evaluation.

  • It does not work with Pinot's multi-value column functions such as DISTINCTCOUNTMV.

Alternatively, from Pinot 0.8, you can use the complex-type handling in ingestion configurations to flatten and unnest the complex structure and convert them into primitive types. Then you can reduce the complex-type data into a flattened Pinot table, and query it via SQL. With the built-in processing rules, you do not need to write ETL jobs in another compute framework such as Flink or Spark.

To process this complex type, you can add the configuration complexTypeConfig to the ingestionConfig. For example:

With the complexTypeConfig , all the map objects will be flattened to direct fields automatically. And with unnestFields , a record with the nested collection will unnest into multiple records. For instance, the example at the beginning will transform into two rows with this configuration example.

Flattened/unnested data

Note that:

  • The nested field group_id under group is flattened to group.group_id. The default value of the delimiter is . You can choose another delimiter by specifying the configuration delimiter under complexTypeConfig. This flattening rule also applies to maps in the collections to be unnested.

  • The nested array group_topics under group is unnested into the top-level, and converts the output to a collection of two rows. Note the handling of the nested field within group_topics, and the eventual top-level field of group.group_topics.urlkey. All the collections to unnest shall be included in the configuration fieldsToUnnest.

  • Collections not specified in fieldsToUnnestwill be serialized into JSON string, except for the array of primitive values, which will be ingested as a multi-value column by default. The behavior is defined by the collectionNotUnnestedToJson config, which takes the following values:

    • NON_PRIMITIVE- Converts the array to a multi-value column. (default)

    • ALL- Converts the array of primitive values to JSON string.

    • NONE- Does not do any conversion.

You can find the full specifications of the table config here and the table schema here.

You can then query the table with primitive values using the following SQL query:

. is a reserved character in SQL, so you need to quote the flattened columns in the query.

Infer the Pinot schema from the Avro schema and JSON data

When there are complex structures, it can be challenging and tedious to figure out the Pinot schema manually. To help with schema inference, Pinot provides utility tools to take the Avro schema or JSON data as input and output the inferred Pinot schema.

To infer the Pinot schema from Avro schema, you can use a command like this:

Note you can input configurations like fieldsToUnnest similar to the ones in complexTypeConfig. And this will simulate the complex-type handling rules on the Avro schema and output the Pinot schema in the file specified in outputDir.

Similarly, you can use the command like the following to infer the Pinot schema from a file of JSON objects.

You can check out an example of this run in this PR.

Last updated

Was this helpful?