Key:value objects

faq
#1

Recently, a Panoply data architect helped me set up a MongoDB collection of user data. But the arch said he came across an issue because of how our source data was structured. One object in particular had a key:value structure. He explained that he added a new column and defined it as a key:value column.

Everything’s fixed and works great now. But my question is: could I have solved or configured this myself? If so, how? Or does Support need to help if we run into this situation again?

#2

Hi Steve,

Short answer: You’re welcome to contact Support, but you can also solve this one yourself if you’d like.

To provide some concrete context for other users, assume we have a sounds.playcount object with a key:value structure, like the following example:

"playcount" {
   "35HSCkvSb6ST9Sk5":1,
   "6AubwFku2J5TdUB8":9,
   "9PUsVawEY3CT2a6y":5
}

This kind of structure adds a new column for each key, which will very quickly cause the table to reach the 1600 column limit (in Redshift).

To solve this, you need to:

  1. Add the column to the parent table.
  2. Change the Nested level configuration by setting Key:Value to true.

In our example case, (1) Add the playcount column to the parent table. Let’s call it flowkey_users_songs for our example. The column type is varchar. We could use syntax like this:

ALTER TABLE flowkey_users_songs ADD COLUMN playcount VARCHAR(256);

After the column is created, (2) define it as a key:value column in the Panoply platform.