Created question in FIWARE Q/A platform on 26-04-2018 at 13:04
Please, ANSWER this question AT https://stackoverflow.com/questions/50041404/how-to-store-hierarchical-data-through-cygnus-with-mongodb

Question:
How to store hierarchical data through Cygnus with MongoDB

Description:
I've deployed a FIWARE configuration to receive MQTT messages from TheThingsNetwork. The configuration uses IoTAgent-JSON, Orion and Cygnus, with a MongoDB backend for Cygnus.
The messages are correctly persisted to MongoDB. Unfortunately, the message data having a hierarchical structure, some parts of the message appear as strings in MongoDB, instead of embedded subdocuments.

This makes it difficult to query the data.

Here are the details:

The message format is defined by the TTN MQTT Data API.

I've defined an entity type on Orion as follows:

curl http://localhost:1026/v2/entities X POST -H "content-type: application/json" -H "fiware-service: myservice" -H "fiware-servicepath: /mypath" -d @ << EOF
{
"id": "TtnMqttMessage",
"type": "TtnMqttMessge",
"app_id":

{ "type": "Text", "value": "my-app-id" }

,
"dev_id":

{ "type": "Text", "value": "my-dev-id" }

,
...
"metadata": { "type": "StructuredValue", "value": {
"airtime": 46336000,
"time": "1970-01-01T00:00:00Z",
...
"gateways":
[

{ "gw_id": "gw1", "timestamp": 12345, "time": "1970-01-01T00:00:00Z", ... "altitude": 6 }

]
} }
}
EOF

In particular, in the configuration above, metadata is a structured value, containing an array of gateways.

On IoTAgent-JSON, a service and a device are provisioned:

curl http://localhost:4041/iot/services X POST -H "content-type: application/json" -H "fiware-service: myservice" -H "fiware-servicepath: /mypath" -d @ << EOF
{
"services": [

{ "apikey": "my_app_id", "entity_type": "TtnMqttMessage", "resource": "/iot/json" }

]
}
EOF

curl http://localhost:4041/iot/devices?options=keyValues X POST -H "content-type: application/json" -H "fiware-service: myservice" -H "fiware-servicepath: /mypath" -d @ << EOF
{
"devices": [

{ "device_id": "my_device_id", "entity_name": "TtnMqttMessage", "entity_type": "TtnMqttMessage", "timezone": "Europe/Zurich", "transport": "MQTT" }

]
}
EOF

Finally, a notification subscription is established from Orion to Cygnus:

curl http://localhost:1026/v1/subscribeContext H "content-type: application/json" -H "fiware-service: myservice" -H "fiware-servicepath: /mypath" -X POST -d @ << EOF
{
"entities": [

{ "type": "TtnMqttMessage", "isPattern": "false", "id": "TtnMqttMessage" }

],
"attributes": [ "app_id", "dev_id", "hardware_serial", "port", "counter", "is_retry", "confirmed", "payload_raw", "payload_fields", "metadata" ],
"reference": "http://cygnus:5050/notify",
"duration": "P100Y",
"notifyConditions": [

{ "type": "ONCHANGE", "condValues": [ "app_id", "dev_id", "payload_raw", "counter" ] }

]
}
EOF

The received messages are persisted to MongoDB. However, the attribute metadata and in particular the array gateways are stored as strings:

> mongo
> ...
> db['..collectionname...'].findOne();
{
"_id" : ObjectId("5adf0b904cedfd001cd72113"),
"recvTime" : ISODate("2018-04-24T10:48:47.605Z"),
"app_id" : "my-app-id",
"confirmed" : "false",
"counter" : "2",
"dev_id" : "my-dev-id",
"hardware_serial" : "0102030405060708",
"is_retry" : "false",
"metadata" : "{\"airtime\":4.6336e+07,\"time\":\"1970-01-01T00:00:00Z\",\"frequency\":868.1,\"modulation\":\"LORA\",\"data_rate\":\"SF7BW125\",\"bit_rate\":50000,\"coding_rate\":\"4/5\",\"latitude\":52.2345,\"longitude\":6.2345,\"altitude\":2,\"gateways\":[

{\"gw_id\":\"gw1\",\"timestamp\":12345,\"time\":\"1970-01-01T00:00:00Z\",\"channel\":0,\"rssi\":-25,\"snr\":5,\"rf_chain\":0,\"latitude\":52.1234,\"longitude\":6.1234,\"altitude\":6}

]}",
"payload_fields" : "{}",
"payload_raw" : "AQIDBA",
"port" : "1"
}

How can I persist the data in a format that can be easily queried? Eg.

with an embedded subdocument under metadata (denormalized form)
or in normalized where metadata is a separate document that references the main document for the message.