Database Functions for Unstructured Data

A lot of NoSQL and non-relational databases (like Mongo, Elastic Search etc) are able to store the data in the form of arrays, JSON and objects. In many cases, we would like to access this unstructured data for creating reports like bursting, flattening, finding key values etc.

There are a couple of options which Helical Insight application provides in order to access this data. The same are described below.

1. Flatten Function: If the data is in the form of an array then flatten database function can be used for converting that into distinct rows.

Data sample : 
{
  "product" : ["Product1”]
  "categories" : ["A","B","C"]
} 

With flatten DB function the data will become something like this

Product	Categories
Product1	A
Product1	B
Product1	C



2. INDEX Function: Index function can also be used to fetch any data of any specific index from the array data.

 
For Data sample : 
{
  "product" : ["Product1”]
  "categories" : ["A","B","C"]
} 

For example with the above function shown in image, the Index function will fetch the 3rd item from the array of the selected column i.e. “C”

3. JSON KeyValue / JSON_Value Functions: When the data is in the form of JSON then the KeyValue or JSON_Value DBFunctions present in JSON category can be used.

Sample data: 
{
product:“MyType”
categories:“12345”
}

If from the above JSON data I want to fetch the data of “product” key then after applying this function the output will return “MyType”







4. JSON K_V_Gen: In the JSON data when K_V_Gen function is applied it will separate out the keys and values of that JSON data.

For example for the below data
{"foo": 1, "bar": 2}

When K_V_Gen is applied the output would be



 
[
  {"key":"foo","value":"1"},
  {"key":"bar","value":2}
]

5. Repeated_Count in NestedData Function: The repeated_count function when applied on an array data will result in the count of items present in that specific array.

For Data sample : 
{
  "product" : ["Product1”]
  "categories" : ["A","B","C"]
}

When we apply Repeated_Count function on “categories” it will result into 3.



For more information you can email on support@helicalinsight.com