You can translate the question and the replies:

Convert a JSON file held in an Oracle Blob to a register

Hi We have JSON files stored in an Oracle DB as BLOBs. We would like to convert the JSON files into a column of type 'Register'. We have ingested the BLOBS into Denodo and converted them into 'text' type columns. We now want to convert the text into a register. We have found the 'JSON_TO_COMPLEX_TYPE' function but this only seems to work using literals as the parameters. Is there a way to use the 'JSON_TO_COMPLEX_TYPE' function but pass in field names? When using the function it does give us the option to select a column but when we try and save the new field we get an error message 'Invalid function 'json_to_complex_type(data_field,specification_field) Any help is very much appreciated. Thank you
user
01-03-2024 03:50:30 -0500
code

2 Answers

Hi I managed to solve this issue using a 'From Variable' JSON data source and a join to the source data as per the details on this page https://community.denodo.com/kb/en/view/document/Using%20the%20From%20Variable%20Data%20route?tag=Data+Sources. Thank you
user
01-03-2024 06:42:35 -0500
Hi, I am glad you found a solution! The [article](https://community.denodo.com/kb/en/view/document/Using%20the%20From%20Variable%20Data%20route?tag=Data+Sources) you linked in your answer provides exellent instruction on how to convert a text field containing JSON into registers. To summarize the solution you gave: * You start off with a base view that represents the data stored in your Oracle database which contains JSON files as BLOBs. Let's call it bv_oracle_json, having been created from a Data Source called ds_oracle. * Then you create a New JSON Data Source, setting the Data Route to "From Variable" and Configure the Variable Name to be something like "json" and save the Data Source. Let's refer to this Data Source as ds_json_variable * With ds_json_variable pulled up, you click "Create base view" and name it something like bv_json_variable. You will be prompted to enter a value for the JSON interpolation variable * Copy and paste one of the json text strings from bv_oracle_json into the JSON interpolation variable prompt. Click Next, then Click OK * The last step is to create a derived view that joins bv_oracle_json with bv_json_variable using bv_oracle_json.json = bv_json_variable.json as the join condition. In the output tab, you can remove both of the json fields. As mentioned in the [article](https://community.denodo.com/kb/en/view/document/Using%20the%20From%20Variable%20Data%20route?tag=Data+Sources) you referenced, Using The From Variable Data Route, creating the JSON Data Source with the "From Variable" data route allows Virtual DataPort to pass the json field found in bv_oracle_json as a parameter to bv_json_variable automatically when performing the join in the derived view. This results in the original json text field being converted into registers. For more information on the various steps involved, view the following resources: [Creating JSON Data Source and Base View](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/json_sources) [Creating Join Views](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/creating_derived_views/creating_join_views) Hope this helps!
Denodo Team
04-03-2024 18:41:14 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here