Extract Single Values from Multidimensional JSON

[andynoodle]

Hello,

I am working with applicant data, where one applicant can apply to multiple jobs. Thus, one "application" JSON object can have many jobs nested in it. The application object should be joined with other objects (candidate, job, etc) to pull in attributes such as candidate name, job status, etc. 

So, I need there to be ONE row per application, so that I can join it with other data. 

However, when I try to "extract" values from the nested job object, I get an error because it is not a simple list, and when I expand to new rows, I lose the ability to join because there are multiple rows with the same application ID. What makes this data difficult is that the jobs are stored in pairs of JobID : JobTitle, and there are hundreds of Job IDs so pivoting the table doesn't help me here. 

How can I extract a single job per application? Most applicants only apply to one job (over 99%), so I'm okay with making some assumptions about which job to pull for those who applied to multiple. 

Here is sample data:

{
  "id": 985314,
  "candidate_id": 978031,
  "prospect": false,
  "applied_at": "2016-03-26T20:11:39.000Z",
  "rejected_at": "2016-08-17T21:08:29.686Z",
  "last_activity_at": "2016-08-27T16:13:15.000Z",
  "source": {
    "id": 1871,
    "public_name": "Happy Hour"
  },
  "credited_to": {
    "id": 4080,
    "name": "Kate Austen",
    "employee_id": null
  },
  "rejection_reason": {
    "id": 8,
    "name": "Lacking skill(s)/qualification(s)",
    "type": {
      "id": 1,
      "name": "We rejected them"
    }
  },
  "rejection_details": null,
  "jobs": [
    {
      "id": 9963781,
      "name": "Accounting Manager"
    },
    {
      "id":9987115
      "name":"Waterboy"
    }
  ],
  "status": "rejected",
  "current_stage": {
    "id": 62828,
    "name": "Recruiter Phone Screen"
  },
  "answers": [
    {
      "question": "Why do you want to work for us?",
      "answer": "I heard you're awesome!"
    },
    {
      "question": "How did you hear about this job?",
      "answer": "From a former colleague."
    }
  ],
  "custom_fields": {
    "bio": "This is a bio",
    "birthday": "1992-01-27"
  },
  "keyed_custom_fields": {
    "date_of_birth": {
      "name": "Birthday",
      "type": "date",
      "value": "1992-01-27"
    },
    "bio": {
      "name": "Bio",
      "type": "long_text",
      "value": "This is a bio"
    }
  }
}

[v-jiascu-msft]

Hi @andynoodle,

Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[andynoodle · Accepted solution]

Dale,

My problem was resolved, but through another method. I didn't realize you can write a query directly in power BI before doing any transformtions. Thus, I just used the language from the DocumentDB api to pull in exactly what I wanted!

[v-jiascu-msft]

Hi @andynoodle,

It seems you already did much. If there is a way to join, do you still want to extract a single job per application? Maybe you could try:

1. Create a new table:

Table =
VALUES ( 'test'[Column1.id] )

2. Establish relationship.

Best Regards!

Dale

[andynoodle]

Dave,

Thanks for the response. Im not sure I understand your question. I want to be able to join the data. Ideally, I could extract all jobs but am fine with just extracting one job per application. The example you showed would give a new table of all the application IDs. I would need to include at least one more column with Job ID(s) to be able to join with Job Information. How would I do this?

[v-jiascu-msft]

Hi @andynoodle,

We can see a "jobs" has two records in your sample. You only want the first record. So we could try to add a new column that would get the first record of "jobs". Remove the old column, expand the new column. Done.

1. Add Custom Column. ("jobs" has 2 records, so it's a list. "FirstJobRecord" is a record because we extract it from a list.)

2. Result. (I have tripled your data to make the sample look realer)

Best Regards!

Dale