Unnest key-value data relating to objects in different columns.

August 14, 2021

An article by Sergey Matrosov

If you have a database, including a table with raw data, where the first column is the user-id/date and the two others are the name and value of your URL parameters as follows:

Table with raw data

and you wish to transform it into a table, such as this:

Transformed table

I created solutions for two databases: Clickhouse and PostgreSQL. This article is based on the former, but here you can also find a query relating to PostgreSQL.

Firstly, we need to generate a data table via a query, using the columns, “userid”, “name” and “value”, as we did when we wrote simple attribution models via BigQuery.

Modifying the table

As a result, we obtain a table as in our first screenshot. Then we need to use the CASE...WHEN construction to mutate the “name” column into columns that have as its name, the values of this column. The value of these new columns will be the value of the column, “value”.

CASE...WHEN construction use

Here is our result:

Modifying the table

The problem is obvious: not only does it have many void cells, but we wanted to have one row for each user. If you considered GROUP BY, you are correct, but we need to use a proper function for this. Firstly, I took an array function called groupArray:

groupArray function use

Don’t forget to use the GROUP BY userid statement:

GROUP BY userid

Here is our modified table:

Modified table

We are almost there; the last function is arrayJoin, which will unnest this array into the usual rows.

arrayJoin function use

And here it is:

final result

Hope this helped you. Good luck!