[Backlogmanager] [FIWARE-JIRA] (HELP-14064) [fiware-stackoverflow] How to store hierarchical data through FIWARE Cygnus with MongoDB

[Andres Muñoz (JIRA)]

     [ https://jira.fiware.org/browse/HELP-14064?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andres Muñoz resolved HELP-14064.
---------------------------------
    Resolution: Done

> [fiware-stackoverflow] How to store hierarchical data through FIWARE Cygnus with MongoDB
> ----------------------------------------------------------------------------------------
>
>                 Key: HELP-14064
>                 URL: https://jira.fiware.org/browse/HELP-14064
>             Project: Help-Desk
>          Issue Type: Monitor
>          Components: FIWARE-TECH-HELP
>            Reporter: Backlog Manager
>            Assignee: Andres Muñoz
>              Labels: fiware, fiware-cygnus, fiware-orion, mongodb
>
> Created question in FIWARE Q/A platform on 26-04-2018 at 13:04
> {color: red}Please, ANSWER this question AT{color} https://stackoverflow.com/questions/50041404/how-to-store-hierarchical-data-through-fiware-cygnus-with-mongodb
> +Question:+
> How to store hierarchical data through FIWARE 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: 
> > 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"
> }
> As can be seen above, the attribute metadata, and in particular the array gateways it contains, are stored as strings and not as JSON subdocuments. 
> 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 form where metadata is a separate document that references the main document for the message.



--
This message was sent by Atlassian JIRA
(v6.4.1#64016)