Extracting Keys from Music Metadata for Custom Property Analysis

·Feb 14, 2025 02:53 AM·

I have an event property called Metadata filters that contains music metadata in an array of objects. For example: [{"Genre":"indie pop"},{"Year":"2023"},{"Artist": "madonna"}] I want to create a custom property from that and extract the keys, so that I can do a breakdown by how often each of the object keys (such as genre, year and artist) were used. Any idea how or if at all possible?

19 comments

· Sorted by Oldest

Johannes K.
·
·
Found a way to kinda do it via REGEX_EXTRACT, but doesn't feel like the best way to do this.
Andrew S.
·
·
You should be able to do breakdowns on arrays directly. E.g.
Andrew S.
·
·
If your property has the array icon you can pick how you want the breakdown to work
Andrew S.
·
·
And you can stack them too.
Johannes K.
·
·
That breaks it down by each value of the key. For example, if I do this by Genre , I get a break down of all genre values. But I'd like to break it down by the key. In other words, I care about the number of times each key occurred and not about the values in each key:value pair. Any idea?
Andrew S.
·
·
In this example, I am measuring the total events (occurrences) where max, min or not set is the key for the filter. Vs the values of those keys.
Andrew S.
·
·
Vs the number of times buckets of values were selected
Johannes K.
·
·
Thanks for that. Sorry Andrew S. I was travelling. In your case, do you have to change the datatype or does it arrive as a List like it does in your screenshot?
Andrew S.
·
·
I did not have to change my datatype. It's how the list results present
Johannes K.
·
·
Okay thanks. In my case the objects come in an array. I.e. there are many key:value pairs which is why this might not work for me. Have to look at how the data is sent. Thanks
Andrew S.
·
·
Double check it was submitted as object lists and not just a string array. Check the icon next to the property name
👍1
Johannes K.
·
·
Andrew S. still stuck on this a little. Do you mind sharing with me how exactly your list looks like? As in, the raw data. Mine looks like this and I suspect we have formatted it incorrectly.

Andrew S.

Lists look like:

"guidedrec_choice_ids": [
      5670,
      5707,
      5734
    ],

and object lists look like:

"guidedrec_choices": [
      {
        "id": 5670,
        "text": "Red"
      },
      {
        "id": 5707,
        "text": "Rich and unctuous stew"
      },
      {
        "id": 5734,
        "text": "Floral"
      },
      {
        "id": 6960,
        "text": "Vanilla-flavoured"
      }
    ],

Johannes K.
·
·
the guiderec_filters from your screenshot is coming from one of those lists? Thank you for posting this. Good for me too see 🙏
Andrew S.
·
·
yes. I looked at it in "json" format in the version I pasted as text.

Johannes K.
·
·
Found a way to kinda do it via REGEX_EXTRACT, but doesn't feel like the best way to do this.
Andrew S.
·
·
You should be able to do breakdowns on arrays directly. E.g.
Andrew S.
·
·
If your property has the array icon you can pick how you want the breakdown to work
Andrew S.
·
·
And you can stack them too.
Johannes K.
·
·
That breaks it down by each value of the key. For example, if I do this by Genre , I get a break down of all genre values. But I'd like to break it down by the key. In other words, I care about the number of times each key occurred and not about the values in each key:value pair. Any idea?
Andrew S.
·
·
In this example, I am measuring the total events (occurrences) where max, min or not set is the key for the filter. Vs the values of those keys.
Andrew S.
·
·
Vs the number of times buckets of values were selected
Johannes K.
·
·
Thanks for that. Sorry Andrew S. I was travelling. In your case, do you have to change the datatype or does it arrive as a List like it does in your screenshot?
Andrew S.
·
·
I did not have to change my datatype. It's how the list results present
Johannes K.
·
·
Okay thanks. In my case the objects come in an array. I.e. there are many key:value pairs which is why this might not work for me. Have to look at how the data is sent. Thanks
Andrew S.
·
·
Double check it was submitted as object lists and not just a string array. Check the icon next to the property name
👍1
Johannes K.
·
·
Andrew S. still stuck on this a little. Do you mind sharing with me how exactly your list looks like? As in, the raw data. Mine looks like this and I suspect we have formatted it incorrectly.

Andrew S.

Lists look like:

"guidedrec_choice_ids": [
      5670,
      5707,
      5734
    ],

and object lists look like:

"guidedrec_choices": [
      {
        "id": 5670,
        "text": "Red"
      },
      {
        "id": 5707,
        "text": "Rich and unctuous stew"
      },
      {
        "id": 5734,
        "text": "Floral"
      },
      {
        "id": 6960,
        "text": "Vanilla-flavoured"
      }
    ],

Johannes K.
·
·
the guiderec_filters from your screenshot is coming from one of those lists? Thank you for posting this. Good for me too see 🙏
Andrew S.
·
·
yes. I looked at it in "json" format in the version I pasted as text.

Extracting Keys from Music Metadata for Custom Property Analysis | Mixpanel Community