Knowledge Base

Introduction to Unnest in NQL

The Unnest feature in Narrative Query Language (NQL) allows users to expand arrays and composite types into a set of rows. This is particularly useful for datasets that include arrays of data, such as phone numbers or addresses, enabling more detailed and granular analysis.

Uploading a Dataset with Arrays

When a user uploads a dataset to the Narrative platform, they can use file formats like CSV, JSON, or Parquet. Below is an example of a dataset schema that includes an array of phone numbers for each user:

Example Schema

Dataset Description:

The dataset contains user information, including an array of phone numbers with details about the phone type.

Sample JSON Schema:

[
    {
        "user_id": 1,
        "username": "john_doe",
        "email": "john@example.com",
        "phone_numbers": [
            {"phone_number": "1234567890", "phone_type": "Mobile"},
            {"phone_number": "9876543210", "phone_type": "Home"}
        ]
    },
    {
        "user_id": 2,
        "username": "jane_smith",
        "email": "jane@example.com",
        "phone_numbers": [
            {"phone_number": "5551112233", "phone_type": "Work"}
        ]
    },
    {
        "user_id": 3,
        "username": "bob_jones",
        "email": "bob@example.com",
        "phone_numbers": [
            {"phone_number": "9998887766", "phone_type": "Mobile"},
            {"phone_number": "4443332221", "phone_type": "Work"}
        ]
    }
]

In this example, each user has an array of phone numbers, where each phone number has an associated type (e.g., Mobile, Home, Work).

Using Unnest

With the Unnest feature, you can extract each phone number and its type from the phone_numbers array and list them in separate rows. Here's how you can do that:

Basic Query

SELECT x.phone_number, x.phone_type
FROM company_data."12345" u, unnest(u.phone_numbers) as x

Result:

This query takes each element of the phone_numbers array and returns them as separate rows, making it easier to analyze each phone number individually.

Naming Columns in Unnest

You can also give custom names to the columns returned by the Unnest function:

SELECT a, b
FROM company_data."12345" u, unnest(u.phone_numbers) as x(a, b)

Result:

This query assigns custom names a and b to the phone_number and phone_type columns, respectively. It's important to remember naming a column is based on the position the column in your query and in

Conclusion

The Unnest feature in NQL is a powerful tool for dealing with arrays and composite types in your datasets. With these examples, you can start using Unnest to simplify and enhance your data querying process within the Narrative platform. By breaking down complex data structures into individual elements, Unnest enables more detailed and granular analysis, making it easier to derive valuable insights from your data.