You can translate the question and the replies:

Flattening json content blob to separate columns

Hi, I am working with certain base views containing json data blobs as a column with the field type as text and not something like arrays.I am looking to flatten the view and get all the keys/values in separate columns. The content json blob column is built upon a combination of Arrays - Name/Value strings but the base view, which I have access to contains this blob in Text field and not as an Array. Below is an example of the records that I am trying to expand to a table view. I've tried the flatten view option but as the json blob is categorized as text, I am not able to use this option. Any help will be greatly appreciated. Thanks | id | contenttype | contentblob | | -------- | -------- | -------- | |18b73 | xyz |{"$type":"xyz","FactorDescription":"Official","Result":"NOFLAG","ResultDescription":"NoFlag","ResultNumericValue":0,"Evidence":{"$type":"xyz","value":0}} |
user
21-09-2020 11:51:29 -0400

1 Answer

Hi, For your scenario, I would do the following steps in Virtual DataPort, 1. Create a new **JSON** data source with data route as "**From Variable**" and in the configure tab, provide the variable name(for example variable_with_data). 1. Click on "Create base view" and in the pop window, I would provide the **value** for that variable(for example :{"$type": "xyz","FactorDescription": "Official","Result": "NOFLAG","ResultDescription": "NoFlag","ResultNumericValue": 0,"Evidence": {"$type ":"xyz ","value ":0}}) 1. Then, create a **Join** between the above created view and existing view(which contain the data) by mapping the variable(i.e., variable_with_data) with common field(i.e., contentblob). For more information, you could refer to the [Path From Variable](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_data_sources_and_base_views/path_types_in_virtual_dataport/from_variable_path) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
22-09-2020 23:42:18 -0400
You must sign in to add an answer. If you do not have an account, you can register here