MongoDB data extraction

[sagarkharpude]

Hi,

I am trying to pull the data from MongoDB. But I am not getting the data in exact format.

I am getting the data as below :

[{ "Address" : "00:0A:F7:2D:2C:D1", "IpAddresses" : [{ "Address" : "10.180.74.14", "IPSubnet" : "255.255.252.0" }, { "Address" : "fe80::b57d:f0e7:3891:bd21", "IPSubnet" : "64" }] }]

{ "InventorySiteId" : "0c396f1f-2139-4590-8c75-4a47ae8bb407", "SiteComputerId" : "0a604b58-1994-4958-b40f-adc8232b74a5" }

[{ "_t" : "DirectX", "Version" : "4.9.0.904" }, { "_t" : "Antivirus", "Name" : "AVG CloudCare AntiVirus", "State" : "262160" }]

How can I cleanse the data from MongoDB.

Thanks

Sagar

[Gysbert_Wassenaar]

That looks like data in a JSON format. If you use the REST connector to pull the data from your mongodb database then the rest connector will convert the json data into tables that Qlikview and Qlik Sense can load.

[sagarkharpude]

Hi Gysbert,

Thanks for the information.

Is there any other way to cleanse the data. Because I think I will need license to use REST connector. We don't have it.

Thanks

[Darren_Ball]

Hi,

What connector or method are you using to extract this data from MongoDB?

As Gysbert suggested the data is coming back in JSON, so if this is how the data is stored then you could, in your Qlik script, save it to a local file and then use the Qlik Web Connectors' General Web Connector, which can read local files and work with JSON.

Or, if the data isn't stored like this in your collection and the format is due to the connection method you are using, then you could try the dedicated Qlik Web Connectors' MongoDB connector.

If you are have the appropriate edition of MongoDB, I believe it's MongoDB Enterprise Advanced, then you can use their dedicated/official MongoDB BI Connector.

[Gysbert_Wassenaar]

The REST connector is free afaik. You can download it from Qlik Market

[sagarkharpude]

Hi,

I am using below load to pull the data :

LOAD *

FROM

[http://localhost:5555/data?connectorID=MongoDBConnector&table=Collection&server=10.0.230.93&port=279...]

(qvx);

As per Gysbert's suggestion I have installed REST connector as well but don't know how to convert the JSON data to xml or Qlikview understandable format.

Can you please provide me any hint.

Thanks

[Darren_Ball · Accepted solution]

Hi,

As the REST connector isn't part of the Qlik Web Connectors, I'm unable to comment on exactly how to use it with JSON.

However, in your load script you should be able to write your table to a local file and then using the Qlik Web Connectors' General Web Connector, you can use the GetFile parameter and enter the path of the file you've created.

So, i think if you add this line to your load script:

store mytablewithJSON into myfolderpath\myfilewithJSON.txt (txt);

this will write the contents of the table to a text file.

You can then pick one of the tables from the General Web Connector, for example JsonToTable:

Click on parameters and select FILE from the request method and entering the full path in the request URI:

Hit run and you should get some script to pop into your Qlik app.

I admit that writing back to disk isn't idea, but this should get you a step closer.

[sagarkharpude]

Hi Darren,

Thanks for the information. But unfortunately we don't have web connector license.

Thanks

Sagar