How can I best convert an API JSON object to a single row for SQL server?

Question

I have a script setup to pull a JSON from an API and I need to convert objects into different columns for a single row layout for a SQL server. See the example below for the body raw layout of an example object:

"answers": {
  "agent_star_rating": {
    "question_id": 145,
    "question_text": "How satisfied are you with the service you received from {{ employee.first_name }} today?",
    "comment": "John was exceptionally friendly and knowledgeable.",
    "selected_options": {
      "1072": {
        "option_id": 1072,
        "option_text": "5",
        "integer_value": 5
      }
    }
  },

In said example I need the output for all parts of agent_star_rating to be individual columns so all data spits out 1 row for the entire survey on our SQL server. I have tried mapping several keys like so:

agent_star_rating = [list(response['answers']['agent_star_rating']['selected_options'].values())[0]['integer_value']]
agent_question = (response['answers']['agent_star_rating']['question_text'])
agent_comment = (response['answers']['agent_star_rating']['comment'])  
response['agent_question'] = agent_question
response['agent_comment'] = agent_comment
response['agent_star_rating'] = agent_star_rating

I get the expected result until we reach a point where some surveys have skipped a field like ['question text'] and we'll get a missing key error. This happens over the course of other objects and I am failing to come up with a solution for these missing keys. If there is a better way to format the output as I've described beyond the keys method I've used I'd also love to hear ideas! I'm fresh to learning python/pandas so pardon any improper terminology!

you can access dictionary with `.get()` which will not throw any error if `key` does not exist, addition to this you can pass on default value to `.get()` if the `value/key` does not exists. You can refer this [question](https://stackoverflow.com/questions/11041405/why-dict-getkey-instead-of-dictkey) — GodWin1100, Aug 03 '22 at 18:56

Tomás Ilabel A. · Answer 1 · 2022-08-03T18:55:53.017

I would do something like this:

# values that you always capture
row = ['value1', 'value2', ...]

gottem_attrs = {'question_id': '' ,
               'question_text': '',
               'comment': '', 
               'selected_options': ''}

# find and save the values that response have
for attr in list(response['agent_star_rating']):
    gottem_attrs[attr] = response['agent_star_rating'][attr]

# then you have your final row
final_row = row + gottem_attrs.values()

If the response have a value in his attribute, this code will save it. Else, it will save a empty string for that value.

How can I best convert an API JSON object to a single row for SQL server?

1 Answers1