Struggling with ODK to PostGres mapping

[Andrew Cawood]

I've got a simple ODK form with 1 repeat group, and am trying to map this to PostGres with little success.
Please could you have a look and see if you can pick up where I am going wrong?

Input (from ODK via JSON publisher):

{
  "token": "",
  "formVersion": "",
  "formId": "phenology001",
  "data": [
    {
      "today": "2019-08-26",
      "start": "2019-08-26T21:09:48.420Z",
      "observation": [
        {
          "stage": "1",
          "picture": {
            "url": "https://<mypictureurl>",
            "type": "image/jpeg",
            "filename": "1566853816709.jpg"
          },
          "notes": "Test note"
        },
        {
          "stage": "2",
          "picture": {
            "url": "https://<mypictureurl2>",
            "type": "image/jpeg",
            "filename": "1566853849716.jpg"
          },
          "notes": "Test note 2"
        }
      ],
      "name": "Andrew",
      "location:Longitude": null,
      "location:Latitude": null,
      "location:Altitude": null,
      "location:Accuracy": null,
      "instanceID": "uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6",
      "imei": "867440036085345",
      "end": "2019-08-26T21:11:03.306Z",
      "date": "2019-08-26",
      "*meta-ui-version*": null,
      "*meta-submission-date*": "2019-08-26T21:11:21.838Z",
      "*meta-model-version*": null,
      "*meta-is-complete*": true,
      "*meta-instance-id*": "uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6",
      "*meta-date-marked-as-complete*": "2019-08-26T21:11:21.838Z"
    }
  ],
  "content": "record",
  "__query_params": {}
}

Job:

each(
  dataPath("data[*]"),
  combine(
    sql( function(state) { return (
      `INSERT INTO log_entry (odk_instance_id, logger_name) VALUES ('`
      + dataValue("*meta-instance-id*")(state) + `', '`
      + dataValue("name")(state) + `');`
    )}) ,
   
    each(
      dataPath("observation[*]"),
      sql( function(state) { return (
      `INSERT INTO log_entry_observation `
      + `(odk_instance_id, stage, notes) VALUES ('`
      +  `123` + `', `
      + dataValue("stage")(state) + `, '`
      + dataValue("notes")(state)  + `')`
      )})
    )
  )
);

Job run output:

Executing SQL statement: INSERT INTO log_entry (odk_instance_id, logger_name) VALUES ('uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6', 'Andrew');
Executing SQL statement: INSERT INTO log_entry_observation (odk_instance_id, stage, notes) VALUES ('123', 1, 'Test note')
Finished.

2 problems:
1. Why is there only 1 log_entry_observation insert statement executed?

I expected the second each to trigger twice for the 2 repeats submitted

2. The result of the job run was no change to my Postgres database (no rows inserted in either table). 

Copy-pasting these statements and running them against the DB created both records successfully, so the SQL is correct.

Also, an initial simple job ignoring the repeats sucessfully inserted a row in the`log_entry` table, so I know the credentials are setup correctly.

Any ideas?

Is there any issue with using combine and sql together?

3. Bonus question :o)  How do I access/create a unique id for the repeat-group records?  e.g. something like <parent_uuid>/observation[1]

Thanks!
Andrew

[Taylor Downs]

Hi Andrew,

I've mocked up a postgres DB with those two tables, and this job is acheiving the desired output:

each(

dataPath('data[*]'),

sql(state => {

return (

`INSERT INTO log_entry (odk_instance_id, logger_name) VALUES ('` +

state.data['*meta-instance-id*'] +

`', '` +

state.data.name +

`');`

);

})

);

each(

dataPath('observation[*]'),

sql(state => {

return (

`INSERT INTO log_entry_observation ` +

`(odk_instance_id, stage, notes) VALUES ('` +

`123` +

`', ` +

state.data.stage +

`, '` +

state.data.notes +

`')`

);

})

);

For your questions:

(1) Is this the same format you use with other language-packages? I don't see people use each(path, combine(operation(...), each(path, operation(...))); very often.

(2) See job above and the documentation for language-common (https://github.com/OpenFn/language-common/blob/master/src/index.js#L247-L270) to understand combine. Maybe it's not behaving how you expect?

(3) How about using alterState before your other operations. You could iterate through that array of observations, assigning a new key to each object with the parent ID number, concatenated with something. (A row count? Not ideal, but I've seen clients in the past do that to output: "abc123-1", "abc123-2", "abc123-3". Maybe there's something truly unique about each record?)

[Andrew Cawood]

Hi Taylor,

Thanks, managed to get the INSERTs running modelled on your suggestion.

Also eventually managed to get the alterState going - posting here below for anyone else wanting to do something similar.

Question: Does `data` always just have a single object, or can it be an array? (Particularly when receiving from ODK Aggregate)

Reason I ask is that

each(

 dataPath('data[*]'),

seems to suggest an array, and I would then need to tweak alterState for that eventuality.

----

Found this example useful in figuring out how to use alterState: https://openfn.github.io/docs/documentation.html#alterstate-alter-state-to-make-sure-data-is-in-an-array

My code:

alterState((state) => {
  //store the meta-instance-id on each observation, so that it can be accessed
  //Also add a counter/index to create a unique id for each observation
  var odk_id = state.data["*meta-instance-id*"];
  var max = state.data.observation.length;

  for (var i = 0; i < max; i++) {
    state.data.observation[i].parentrec = odk_id;
    state.data.observation[i].unique = odk_id + "-"+ i.toString();
  }
 
  return state;
});

In response to your question: my previous use of OpenFn was for posting ODK to SalesForce, and the pattern you describe is as per your docs.

Thanks again,

Andrew

[Taylor Downs]

Hey Andrew, great! And good question.

When OpenFn.org spins up a Node.js runtime to execute your job, we pass in `state`. State is a JSON object and contains, at least, two keys that are, themselves, objects. It looks like this:

```json

{

  "configuration": { ... },

  "data": { ... }

}

```

"configuration" contains login and authentication information from an associated "credential", and "data" contains the body of the associated message (which triggered the run) if applicable. When runs are triggered by a timer they inherit the final state of the last run, when they are triggered by another run, they get the final state of that triggering run.

The tricky bit here is that ODK sends a JSON payload to OpenFn which itself includes a "data" key. And that data key, no matter whether the payload contains 1 or 1,000 form submissions, is always an array! This has nothing to do with OpenFn, but it's the format of ODK's outbound message.

So, in order to do anything with ODK data you need to iterate over that data array, which now lives at `state.data.data`. If it's a single submission, you could access the submission data via `state.data.data[0].something`. In your case, you're performing some operation (executing an SQL query) for each item in the `state.data.data` array as "dataPath('data[*]')" is shorthand for that path.

Using each here, and EACHing over the state.data.data array is just because that's the shape of the ODK Aggregate data, but not part of how OpenFn sets up your Node environment, per se.

--
You received this message because you are subscribed to the Google Groups "OpenFn Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openfn+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openfn/569a310a-5942-4fd1-820e-7951d3f7463e%40googlegroups.com.