I have imported JSON data from Hive database. The structure looks like the attached. JSON data has been dumped to Hive without normalizing. Is it possible to parse the data?. For example, in the attached image, the mentionedlocations
column has some places mentioned and I want them to be in separate rows.
You can use the Json.Document
function to read the column as JSON.
I'd suggest creating a custom column with this formula:
Record.ToTable(Json.Document([mentionedlocations]))
and then expanding that column to get the multiple rows you want.
Putting these together:
= Table.ExpandTableColumn(
Table.AddColumn(PreviousStep, "Custom",
each Record.ToTable(Json.Document([mentionedlocations]))),
"Custom", {"Name"}, {"locations"})
This takes the PreviousStep
in the query, adds a Custom
column which converts the JSON text into a table and then expands the Name
column in each of the tables in the Custom
column and renames the column locations
.