Table function similar to Snowflake lateral flatten

Open Q & A
1

I’m trying to import some Key-Value data from a JSON file in S3 and each key becomes a new column in Rockset. I tried looking for something similar to the LATERAL FLATTEN table function in Snowflake. Do I need to make a custom UDF or is there an existing function that could do this?

image
image1671×333 19.6 KB

Example of how I transform this data in Snowflake:

SELECT f.key::VARCHAR                     AS audience_id,
           f.value:contact_ids::ARRAY         AS contact_ids,
           f.value:last_calculated::TIMESTAMP AS last_calculated_at
      FROM raw a,
   LATERAL FLATTEN(input=>a.v) f
2

Hi @jonathan,

You may be looking for the UNNEST table function. This allows you to convert an array/object column into multiple rows, which is similar to the FLATTEN function in Snowflake.

Note that UNNEST is not supported in ingest transformation queries, but you can create a View on top of your collection that uses UNNEST to post-process your data.

3

In my particular case, what I did to solve the issue was I restructured the data slightly so all of the top-level keys were the same:

Before:

[
    { "filter_cache_1234": {
            "contact_ids": [...],
            "last_calculated": ...,
            ...
        }
    },
    ...
]

After:

[
    { 
        "audience_id": "filter_cache_1234",
        "contact_ids": [...],
        "last_calculated": ...,
        ...
    },
    ...
]