Flatten JSON Data

Turn data ingested as a JSON string into tabular data in a batch transform. To use data in downstream features, such as identity resolution and segmentation, the data must be stored as rows and columns in a data lake object or data model object. You can flatten JSON data that’s stored as a single object, an array of objects, or an array of literals (scalar values).

A schema file describing the structure and content of the JSON data is required to flatten nested JSON. This file can be created manually or generated from a sample of your JSON data using a web utility.

Note :

Only the 'type', 'properties', and 'items' keywords are recognized from the schema file; all others are ignored.

The row will not be processed and included in the results if the JSON data is malformed.

The maximum JSON level limit is 6. The 'properties' keyword has a maximum limit of 50.

1. In a transform node, select the Flatten JSON transformation.
2. Select Upload File, and select your JSON schema file.
3. Enter a transformation name.
4. Add at least one output table.
   1. Select Add New Table.
   2. Enter a name for the table.
   3. Select one or more fields to extract from the JSON data.
5. To save the configuration, click Apply.
6. To preview table data, add a node to the output branch for that table.

Example Your data table includes the fields order_id and json_data. Order_id contains an integer that identifies the order. Json_data contains JSON data strings with important information about each order. You want to extract data about orders into tables so that you can calculate insights, such as customer lifetime value.

order_id	json_data
1	{"customer": "John", "items": [{"id": 101, "name": "Laptop", "price": 1500}, {"id": 102, "name": "Mouse", "price": 20}]}
2	{"customer": "Alice", "items": [{"id": 103, "name": "Keyboard", "price": 50}, {"id": 104, "name": "Monitor", "price": 300}]}

To ensure that Data 360 can properly interpret your JSON data, upload a schema file that conforms to the JSON schema specifications. This sample schema file describes the data in the data table.

{
  "type": "object",
  "properties": {
    "customer": {
      "type": "string"
    },
    "items": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": {
            "type": "number"
          },
          "name": {
            "type": "string"
          },
          "price": {
            "type": "number"
          }
        },
        "required": ["id", "name", "price"]
      }
    }
  },
  "required": ["customer", "items"]
}