Parsing Multiple Snowflake Objects with consistent keys to rows-CodePudding

First post, hope I don't do anything too crazy I want to go from JSON/object to long in terms of formatting.

I have a table set up as follows (note: there will be a large but finite number of 50 activity columns, 2 is a minimal working example). I'm not concerned about the formatting of the date column - different problem.

customer_id(varcahr), activity_count(object, int), activity_duration(object, numeric)

CodePudding user response：

@Lukasz Szozda gets close but the answer doesn't scale as well with multiple variables (it's essentially a bunch of cartesian products and I'd need to do a lot of ON conditions). I have a known constraint (each field is in a strict format) so it's easy to recycle the key.

After WAY WAY WAY too much messing with this (off and on searches for weeks) it finally snapped and it's pretty easy.

SELECT
  id, key, activity_count[key], activity_duration[key], activity_duration2[key]
FROM smpl, LATERAL flatten(input => activity_count);

You can also use things OTHER than key such as index

It's inspired by THIS link but I just didn't quite follow it. https://stackoverflow.com/a/36804637/20994650