title: MongoDB - Document Structure to create matrix from multiple value pairs
body: <p>I am new to NoSQL and MongoDB, so please don't bash. I have used SQL databases in the past, but am now looking to leverage the scalability of NoSQL.  One application that comes to mind is the collection of experimental results, where they are serialized in some manner with a start date, end date, part number, serial number, etc.  Along with each experiment, there are many "measurements" collected, but the list of measurements may be unique in each experiment.</p>
  
  <p>I am looking for ideas in how to structure the document to achieve the follow tasks:</p>
  
  <p>1) Query based on date ranges, part numbers, serial numbers<br>
  2) See resulting table in a "spreadsheet" table<br>
  3) Perform statistical calculats, perhaps with R, on the different "measurements"</p>
  
  <p>An example might look like:</p>
  
  <pre><code>[
    {
      "_id": {
        "$oid": "5e680d6063cb144f9d1be261"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583841600000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842007000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3102",
      "Status": "Acceptable",
      "Results": [
        {
          "Sensor": "Pressure",
          "Value": "14.68453",
          "Units": "PSIA",
          "Flag": "1"
        },
        {
          "Sensor": "Temperature",
          "Value": {
            "$numberDouble": "68.43"
          },
          "Units": "DegF",
          "Flag": {
            "$numberInt": "1"
          }
        },
        {
          "Sensor": "Velocity",
          "Value": {
            "$numberDouble": "12.4"
          },
          "Units": "ft/s",
          "Flag": {
            "$numberInt": "1"
          }
        }
      ]
    },
    {
      "_id": {
        "$oid": "5e68114763cb144f9d1be263"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583842033000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842434000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3103",
      "Status": "Acceptable",
      "Results": [
        {
          "Sensor": "Pressure",
          "Value": "14.70153",
          "Units": "PSIA",
          "Flag": "1"
        },
        {
          "Sensor": "Temperature",
          "Value": {
            "$numberDouble": "68.55"
          },
          "Units": "DegF",
          "Flag": {
            "$numberInt": "1"
          }
        },
        {
          "Sensor": "Velocity",
          "Value": {
            "$numberDouble": "12.7"
          },
          "Units": "ft/s",
          "Flag": {
            "$numberInt": "1"
          }
        }
      ]
    },
    {
      "_id": {
        "$oid": "5e68115f63cb144f9d1be264"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583842464000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842434000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3104",
      "Status": "Acceptable",
      "Results": [
        {
          "Sensor": "Pressure",
          "Value": "14.59243",
          "Units": "PSIA",
          "Flag": "1"
        },
        {
          "Sensor": "Weight",
          "Value": {
            "$numberDouble": "67.93"
          },
          "Units": "lbf",
          "Flag": {
            "$numberInt": "1"
          }
        },
        {
          "Sensor": "Torque",
          "Value": {
            "$numberDouble": "122.33"
          },
          "Units": "ft-lbf",
          "Flag": {
            "$numberInt": "1"
          }
        }
      ]
    }
  ]
  </code></pre>
  
  <p>Another approach might be:</p>
  
  <pre><code>[
    {
      "_id": {
        "$oid": "5e680d6063cb144f9d1be261"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583841600000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842007000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3102",
      "Status": "Acceptable",
      "Pressure (PSIA)" : "14.68453",
      "Pressure - Flag": "1",
      "Temperature (degF)": "68.43",
      "Temperature - Flag": "1",
      "Velocity (ft/s)": "12.4",
      "Velocity Flag":  "1"
    },
    {
      "_id": {
        "$oid": "5e68114763cb144f9d1be263"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583842033000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842434000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3103",
      "Status": "Acceptable",
      "Pressure (PSIA)" : "14.70153",
      "Pressure - Flag": "1",
      "Temperature (degF)": "68.55",
      "Temperature - Flag": "1",
      "Velocity (ft/s)": "12.7",
      "Velocity Flag":  "1"
    },
    {
      "_id": {
        "$oid": "5e68115f63cb144f9d1be264"
      },
      "StartDate": {
        "$date": {
          "$numberLong": "1583842464000"
        }
      },
      "EndDate": {
        "$date": {
          "$numberLong": "1583842434000"
        }
      },
      "PartNumber": "1Z45NP7X",
      "SerialNumber": "U84A3104",
      "Status": "Acceptable",
      "Pressure (PSIA)" : "14.59243",
      "Pressure - Flag": "1",
      "Weight (lbf)": "67.93",
      "Weight - Flag": "1",
      "Torque (ft-lbf)": "122.33",
      "Torque - Flag": : "1"
    }
  ]
  </code></pre>
  
  <p>An example table might look like (probably with correct spacing):</p>
  
  <pre><code>StartDate            EndDate              PartNumber SerialNumber Pressure 'Pressure - Flag' Temperature 'Temperature - Flag' Velocity 'Velocity - Flag' Torque 'Torque - Flag' Weight 'Weight - Flag'
  2020-03-10T12:00:00Z 2020-03-10T12:06:47Z 1Z45NP7X   U84A3102     14.68453  1             68.43       1                    12.4     1                  N/A    N/A             N/A      
  N/A
  2020-03-10T12:07:13Z 2020-03-10T12:13:54Z 1Z45NP7X   U84A3103     14.70153  1             68.55       1                    12.7     1                 N/A    N/A             N/A      
  N/A 
  2020-03-10T12:07:13Z 2020-03-10T12:13:54Z 1Z45NP7X   U84A3104     14.59243  1               N/A         N/A                  N/A      N/A                  67.93  1               122.33      
  1 
  </code></pre>
  
  <p>Any thoughts on the best structure?  In reality, there might be 200+ "sensor values".</p>
  
  <p>Thanks,</p>
  
  <p>DG</p>