Skip to main content
In this recipe we’ll learn how to flatten nested objects when ingesting JSON documents into Apache Pinot.

​
Prerequisites

To follow the code examples in this guide, you must install Docker locally and download recipes.
  1. If you haven’t already, download recipes.
  2. In terminal, go to the recipe by running the following command:

​
Launch Pinot Cluster

You can spin up a Pinot Cluster by running the following command:
This command will run a single instance of the Pinot Controller, Pinot Server, Pinot Broker, and Zookeeper. You can find the docker-compose.yml file on GitHub.

​
Dataset

We’re going to import the following JSON file:
data/users.json
Each document has a nested address and company that we’re going to flatten so that we can import all the fields into Pinot.

​
Pinot Schema

Working out all the column names and their types is tedious with complex JSON documents, but luckily Pinot provides a tool that infers the schema for us. We’ll call it by running the following command:
The generated schema file, which gets written to /data/schema/users.json, looks like this:
config/schema.json We can see that those nested properties have been converted into individual columns e.g. address_street, address_geo_lat, company_name.
The . character is used as the default delimiter, but we have overriden that by passing in the -delimiter parameter. We’ll need to make sure that we pass in this delimiter to our table config later on in this guide.
We’ll need to create two copies of the schema file as Pinot requires each table to have its own schema. We also need to update the schemaName to be users_flatten in one and users_no_flatten in the other.

​
Pinot Table

Now let’s create Pinot tables that uses the above schemas. We’re going to create two different tables so that we can see what happens if we don’t specify the config that flattens JSON documents. First, the table config that doesn’t do any flattening:
config/table-no-flatten.json And now one that has the flattening config:
config/table-flatten.json The highlighted config flattens the nested JSON fields. We need to specify delimiter if our schema doesn’t use the . character to separate nested field names. You can create the tables by running the following commands:

​
Ingestion Job

Now we’re going to import the JSON file into Pinot. We’ll do this with the following ingestion spec:
config/job-spec.yml The import job will map fields in each JSON document to a corresponding column in the users schema. You can run the following command to run the import:

​
Querying

Once that’s completed, navigate to localhost:9000/#/query and we’ll run some queries see how the JSON flattening works. First, let’s query users_no_flatten:
You will see the following output: Query Results We can see that the nested fields all have null values. Now let’s try the same query on the users_flatten table:
You will see the following output: Query Results On this table the flattened fields are all hydrated with values from the source data.