In this specific blog we would be covering db specific functions which are specific to NoSQL databases like Mongo etc present in the version of Helical Insight 4.1. Some of these functions like handling of JSON are now also are present for newer versions of databases like Postgress, MySQL etc
JSON Specific function
1) You can select a table column which has JSON object data -> generate report.
2) Let us say that your JSON object is like
EX: {“key1”:”abc”,”key2”:”def”, “key3”:”ghi”}
3) If you want to get the value of key1, drag that column. Now click on the Column -> More -> Advanced -> In Data type select JSON -> In Functions double-click on KEY_VALUE -> below you can see the function selected.
4) At the column , drag and drop the column and at property provide the key , Save the function and generate report…now you can see the result for key (EX : abc) (REFER TO BELOW IMAGES)
Nested JSON Object
– If we have Nested JSON object , then we have to create views at the metadata level.
– Here in below image ‘order_count’ has a nested JSON obj with index ‘sub_count’.
– Ex:{“sub_count”{ {“key1”:”abc”,”key2”:”def”, “key3”:”ghi”}}
– Save the view that you create, save metadata. Now at report level select the column from created view and Click on column -> More -> Advanced -> JSON -> KEY_VALUE -> drag the column and give the key whose value is necessary.
– We can also get the value of key from Nested JSON object using custom column also
– Create a custom column. Give ‘pharmacy_drug_dispenses_counts’.’order_count’.’sub_count’.’key1’
Ex : in your case it will be like ‘Collection_name’.’Table_name’.’Column_name’.’outer_index_inJSONobj’.
FUNCTIONS RELATED TO ARRAYS :
1) When you have arrays as data in table columns , then there are specific functions that you can use.
2) Make sure that in apache drill settings, search for exec.enable_union_type and if it is true , change it to false and update.(if it is selected as true you may not be able to view array related data in the report. It may show as an empty array []).
3) In order to view data in array, Go to the custom column -> give syntax : flatten(‘Table_name’.’column_name’.’array_index’).
4) Flattening will give you the values present in the array.
5) REFER TO BELOW IMAGE.
– We can also achieve this flattening by creating a view at metadata level, and use this view
REFER TO BELOW IMAGE
– Drag the column from metadata view -> click -> More -> Advanced -> JSON -> KEY_VALUE -> drag the column into key_value function and at property give index array.
– Flattening can also be achieved in this way.
– Click on the column -> More -> Advanced -> In Data Type click on nestedData -> Double click on Flatten -> Drag the column (which has array values) into the Flatten function and SAVE.
Note : When we use custom columns there should be atleast one dragged column from the table either as a column to be displayed in table or as a filter…If not it may give error. Also at the custom column we should always write fully qualified SQL.