You can translate the question and the replies:

Parsing JSON file

Hi there, I face the following challenge when trying to create a base view from a JSON file. The content of the file is as follow: {"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx"} {"key1":"xxxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx","key5":"xxxx"} {"key1":"xxxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx","key5":"xxxx"} . {"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx"} Is there a way to read such file ? I have tried with JSON type data source but it only gives the first row. I have tried also to read the file as delimited text but it doesn't work. If someone here has already faced the same challenge, would you please help me on this. Thanks in advance
user
07-09-2023 10:23:37 -0400
code

3 Answers

Hi, One issue I’ve noticed regarding the data itself is that it doesn’t completely match the expectations of a JSON. This is likely why using the JSON data source is returning unexpected results. Instead, the data should be formatted as such: ``` [{"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx"}, {"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx", "key5":"xxxxx"}, {"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx", "key5":"xxxxx"}, {"key1":"xxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx"}] ``` So, create a JSON Data Source, and during base view creation, uncheck ‘Json root’, and paste ‘/JSONFile/JSONArray’ as the Tuple root. This will communicate to the Design Studio that the format of the JSON data is of the following structure: ``` [ {“field”: value, “field”: value}, { “field”: value, “field”: value} ] ``` Hope this helps!
Denodo Team
07-09-2023 14:05:58 -0400
code
Hi, Thanks for your quick reply and for these information. Unfortunately, the format of the file does not contain the braket. It is of JSON Lines format. I have tried to read the file as delimited text and able to get all the rows, but it only outputs one column that contain the full object as string.
user
07-09-2023 14:39:05 -0400
Hi, I would first set up your local file into a delimited data source. This data source will read full lines, as you had already accomplished. Then, I would create a [wrapper](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/generating_wrappers_and_data_sources/generating_wrappers_and_data_sources) to standardize the raw data. Here is an example of what that looks like: ``` CREATE OR REPLACE DATASOURCE DF ds_jsonl_file FOLDER = '/1 - data sources' ROUTE LOCAL 'LocalConnection' '//path to file//' COLUMNDELIMITER = '\n' ENDOFLINEDELIMITER = '\n' HEADER = FALSE; CREATE OR REPLACE WRAPPER DF ds_jsonl_file FOLDER = '/1 - data sources' DATASOURCENAME=ds_jsonl_file OUTPUTSCHEMA ( column0 = 'Column0' (OPT) ); ``` Then, create your base view from the delimited data source. Next, create a JSON data source, and a wrapper, to make sure the data source is aware of the structure of the raw data (since it is JSON Lines). For the JSON data source, I used the ‘[From Variable](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_data_sources_and_base_views/path_types_in_virtual_dataport/from_variable_path)’ data path, with the interpolation variable being ‘input_line’. Please see below for example code. ``` CREATE OR REPLACE DATASOURCE JSON ds_fromlines FOLDER = '/1 - data sources' ROUTE LOCAL 'VariableConnection' 'input_line'; CREATE OR REPLACE WRAPPER JSON ds_fromlines FOLDER = '/1 - data sources' DATASOURCENAME=ds_fromlines TUPLEROOT '/JSONFile' OUTPUTSCHEMA (jsonfile = 'JSONFile' : REGISTER OF ( input_line = 'INPUT_LINE' : 'java.lang.String' (OBL) (DEFAULTVALUE='{"key1":"xxxxx","key2":"xxxxx","key3":"xxxxx","key4":"xxxxx","key5":"xxxx"}') EXTERN, key1 = 'key1' : 'java.lang.String', key2 = 'key2' : 'java.lang.String', key3 = 'key3' : 'java.lang.String', key4 = 'key4' : 'java.lang.String', key5 = 'key5' : 'java.lang.String' ) ); ``` Next, I created the base view from the JSON data source. From here, we can join our delimited base view and our JSON base view to reach the desired outcome of tabular data from JSON Lines. See below for example code. ``` CREATE OR REPLACE TABLE ds_fromlines I18N us_est ( input_line:text (extern), key1:text, key2:text, key3:text, key4:text, key5:text ) FOLDER = '/1 - data sources' CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD ds_fromlines( I18N us_est CONSTRAINTS ( ADD input_line (=) OBL ONE ADD key1 NOS ZERO () ADD key2 NOS ZERO () ADD key3 NOS ZERO () ADD key4 NOS ZERO () ADD key5 NOS ZERO () ) OUTPUTLIST (key1, key2, key3, key4, key5 ) WRAPPER (json ds_fromlines) ); CREATE OR REPLACE VIEW joined_view FOLDER = '/1 - data sources' AS SELECT ds_fromlines.key1 AS key1, ds_fromlines.key2 AS key2, ds_fromlines.key3 AS key3, ds_fromlines.key4 AS key4, ds_fromlines.key5 AS key5 FROM ds_jsonl_file AS ds_jsonl_file INNER JOIN ds_fromlines AS ds_fromlines ON ds_jsonl_file.column0 = ds_fromlines.input_line ; ``` Please see [how to use the from variable data path](https://community.denodo.com/kb/en/view/document/Using%20the%20From%20Variable%20Data%20route) with joins module for more information. Hope this helps!
Denodo Team
08-09-2023 13:57:38 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here