Skip to main content

When you export the Amplitude data into Amazon Redshift the Group Properties come out as a json object something like this as an example

 

{

"Account Name": {

      "MadeUpCompany": {

                    "Plan": “Premium”,

                    "monthlySpend": "5.00",

                   }

               }

}

 

For both user and event properties you can just call the json property in the SQL as it isn’t nested - user_properties.plan or event_properties.OSversion 

 

However in the Group Properties function (if you have it enabled) it has the Account Name first so you can’t access the properties like this

 

group_properties.plan or group.monthlySpend

 

Any reference to those needs to contain the company name - such as 

 

group_properties.MadeUpCompany.Plan = “Premium”

 

This isn’t workable in SQL as every customer has a different customer name. Is there another to extract at this level in Amazon Redshift - has anyone found a simple solution?

Hi @Adamskig 

I have a similar setup but with Snowflake.

Due to the double nested structure, the default approach of accessing a specific company’s record is a bit weird  since we need to put in the customer/org name every time as you have highlighted.

If the actual need is to query just a list of accounts with “premium” plan, then you can just use a LIKE function e.g group_properties:MadeUpCompany LIKE ‘%Premium%.

Else,we would need to transform and process this json schema in another table to get the attributes as individual columns for the usual SQL working.

Hope this helps.

Thanks! - I’ll give it a try and post the findings here!

Update: In the end we plan to unnest the json manually for a backfill in a joining table based on UUID - (looked at EventID - its not unique to events), then update the joining table automatically on a scheduled basis so we can query events from a single group/account property

 

Amplitude - please remove this double nesting for group properties at some point…!

 

 

@Adamskig noted! Feedback sent!

Reply


Terms & ConditionsCookie settings