General Discussions

 View Only

Parsing SP Proposal Sponsors in JSON output from the API

  • 1.  Parsing SP Proposal Sponsors in JSON output from the API

    CAYUSE CHAMPION
    Posted 05-10-2023 14:19

    We are retrieving Proposals via a call like:

     https://developer.app.cayuse.com/v1/proposals?page=5&size=50

    In the JSON output, the Sponsor question using the org-finder widget looks like

          "form": [
            {
              "label": "Sponsor",
              "type": "select",
              "cayuseType": "org-finder",
              "section": "General Info",
              "shortLabel": "Sponsor",
              "answerKey": "Sponsor",
              "answer": "[81c914bf-c589-43c1-85b6-ea66ac2e23e6, 587eb6c9-cfa6-42ad-8d4f-5acad7bea53d]"
            },

    I want to parse the list of sponsors and save to a CSV file. Given that the value of "answer" is not a JSON array, this becomes non-trivial.

    For reference, if your form has a research-team widget question, compare that value to this. The research-team's "answer" is a valid JSON string.

              "label": "List the project team members, starting with the Principal Investigator.",
              "type": "researchTeam",
              "cayuseType": "research-team",
              "section": "Personnel",
              "shortLabel": "Research Team",
              "answerKey": "research-team",
              "answer": "[{\"member\":\"5f0cf208-a6c3-47e5-a209-7d4b8c7002ca\",\"role\":\"principal-investigator\",\"associations\":[{\"association\":\"651a6b87-a7b6-4f23-a4e5-a7ce1f5802a2\",\"credit\":0,\"costShareEffort\":{\"calendar\":0,\"academic\":0,\"summer\":0},\"sponsoredEffort\":{\"calendar\":0,\"academic\":0,\"summer\":0}}]}]"
            },

    Had Sponsors's "answer" been a valid JSON array it would look like

          "form": [
            {
              "label": "Sponsor",
              "type": "select",
              "cayuseType": "org-finder",
              "section": "General Info",
              "shortLabel": "Sponsor",
              "answerKey": "Sponsor",
              "answer": "[\"81c914bf-c589-43c1-85b6-ea66ac2e23e6\", \"587eb6c9-cfa6-42ad-8d4f-5acad7bea53d\"]"
            },

    However, using jq, it is still possible to parse this.

    The jq expression I came up with to parse the "answer" value into a CSV file is:


    jq -r '.proposals[]| .proposalId as $pid |  (.form[] | select(.answerKey=="Sponsor").answer)//" "| sub("\\[";($pid + ", ")) | sub("\\]";"")| split(", ") | [$pid] + _nwise(1) | @csv ' proposals.json

    Which produces output like

    "fcf51c9d-30ca-4136-8e8c-6e1f15950c87","81c914bf-c589-43c1-85b6-ea66ac2e23e6"
    "0095af41-3216-4e63-aba5-7adb2c15a810"," "

    where the first column is the proposalID and the second is the sponsorID. This is not perfect in that a blank sponsor comes through as a space, but probably good enough to send to a database import process.

    I am curious to know how others are handling importing Sponsors via the API. I'm hoping there's a better jq expression out there.

    Thanks!




    ------------------------------
    David McMeans
    Data Engineer
    Wright State University
    ------------------------------