I have this table with several columns containing dictionaries: payloadKV, metaKV, etc.

I need to unnest the dict and pivot the result to put each key in a column and the value in the correspondent cell of that row,column. The desired output of the screenshot above would be:

 --------------------- ------- --------------- ---------------- --------------------- ----- 
|   ingestTimestamp   |  ...  | metadata.Area |  metadata.Cell | metadata.Department | ... |
 --------------------- ------- --------------- ---------------- --------------------- ----- 
| 2022-03-23 02:34:41 |   ... | MC            |           0010 |                0752 | ... |
| ...                 |   ... | ...           |            ... |                 ... | ... |
 --------------------- ------- --------------- ---------------- --------------------- ----- 

Each of these dictionaries have an arbitrary number of key/values, which can be hundreds, and I cannot know the key names beforehand, so I need some generic expression to extract them.

I have seen examples how to extract the desired keys by hardcoding them, but I cannot seem to find a generic way to do it.

CodePudding user response：

In databases rows are observations and columns describe these observations. You want to non-consistently describe observations - that's a meta level of databases who are only meant to be consistent in this respect.

You can