title: Deeply nested JSON response to pandas dataframe
body: <p>I am new to python/pandas and I am having some issues with converting a nested JSON to pandas dataframe. I am sending a query to a database and getting a JSON string back. </p>
  
  <p>It's a deeply nested JSON string that contains several arrays. The response from the database contains thousands of rows. Here is the general structure of one row in the JSON string:</p>
  
  <pre><code>{
    "ID": "123456",
    "profile": {
      "criteria": [
        {
          "type": "type1",
          "name": "name1",
          "value": "7",
          "properties": []
        },
        {
          "type": "type2",
          "name": "name2",
          "value": "6",
          "properties": [
            {
              "type": "MAX",
              "name": "",
              "value": "100"
            },
            {
              "type": "MIN",
              "name": "",
              "value": "5"
            }
          ]
        },
        {
          "type": "type3",
          "name": "name3",
          "value": "5",
          "properties": []
        }
      ]
    }
  }  
  {
    "ID": "456789",
    "profile": {
      "criteria": [
        {
          "type": "type4",
          "name": "name4",
          "value": "6",
          "properties": []
        }
      ]
    }
  }
  </code></pre>
  
  <p>I want to flatten this JSON string using python pandas. I had problems using json_normalize since this is a deeply nested JSON string:</p>
  
  <pre><code>from cassandra.cluster import Cluster
  import pandas as pd
  from pandas.io.json import json_normalize
  
  def pandas_factory(colnames, rows):
      return pd.DataFrame(rows, columns=colnames)
  
  cluster = Cluster(['xxx.xx.x.xx'], port=yyyy)
  session = cluster.connect('nnnn')
  
  session.row_factory = pandas_factory
  
  json_string = session.execute('select json ......')
  df = json_string ._current_rows
  df_normalized= json_normalize(df)
  print(df_normalized)
  </code></pre>
  
  <p>When i run this code, i get a Key error:</p>
  
  <pre><code>KeyError: 0
  </code></pre>
  
  <p>I need help converting this JSON string to a dataframe with only some selected columns that looks something like this: (The rest of the data can be skipped)</p>
  
  <pre><code>ID        |   criteria   | type   |   name   |   value   |
  
  123456          1          type1      name1        7      
  123456          2          type2      name2        6  
  123456          3          type3      name3        5    
  456789          1          type4      name4        6
  </code></pre>
  
  <p>I tried to find similar problems on here but I can't seem to apply it to my JSON string. </p>
  
  <p>Any help is appreciated! :)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>The json string that is retured is a <em>query response object: ResultSet</em> . I think thats why I'm having some issues with using:  </p>
  
  <pre><code>json_string= session.execute('select json profile from visning')
  temp = json.loads(json_string)
  </code></pre>
  
  <p>and getting the error: </p>
  
  <pre><code>TypeError: the JSON object must be str, not 'ResultSet'
  </code></pre>
  
  <p><strong>EDIT #2:</strong></p>
  
  <p>Just to see what I'm working with, I printed the the result query by using: </p>
  
  <pre><code>for line in session.execute('select json.....'):
      print(line)
  </code></pre>
  
  <p>and got something like this: </p>
  
  <pre><code>Row(json='{"ID": null, "profile": null}')
  Row(json='{"ID": "123", "profile": {"criteria": [{"type": "type1", "name": "name1", "value": "10", "properties": []}, {"type": "type2", "name": "name2", "value": "50", "properties": []}, {"type": "type3", "name": "name3", "value": "40", "properties": []}]}}')
  Row(json='{"ID": "456", "profile": {"criteria": []}}')
  Row(json='{"ID": "789", "profile": {"criteria": [{"type": "type4", "name": "name4", "value": "5", "properties": []}]}}')
  Row(json='{"ID": "987", "profile": {"criteria": [{"type": "type5", "name": "name5", "value": "70", "properties": []}, {"type": "type6", "name": "name6", "value": "60", "properties": []}, {"type": "type7", "name": "name7", "value": "2", "properties": []}, {"type": "type8", "name": "name8", "value": "7", "properties": []}]}}')
  </code></pre>
  
  <p>The issue I'm having is converting this structure to a json string that can be used in json.loads(): </p>
  
  <pre><code>json_string= session.execute('select json profile from visning')
  json_list = list(json_string)
  string= ''.join(list(map(str, json_list)))
  temp = json.loads(string) &lt;-- creates error json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
  </code></pre>
  
  <p><strong>EDIT #3:</strong></p>
  
  <p>As requested below in the comments, printing    </p>
  
  <pre><code>for line in session.execute('select json.....'):
  print((line.json))
  </code></pre>
  
  <p>gets the output: </p>
  
  <pre><code>{"ID": null, "profile": null}
  {"ID": "123", "profile": {"criteria": [{"type": "type1", "name": "name1", "value": "10", "properties": []}, {"type": "type2", "name": "name2", "value": "50", "properties": []}, {"type": "type3", "name": "name3", "value": "40", "properties": []}]}}
  {"ID": "456", "profile": {"criteria": []}}
  {"ID": "789", "profile": {"criteria": [{"type": "type4", "name": "name4", "value": "5", "properties": []}]}}
  {"ID": "987", "profile": {"criteria": [{"type": "type5", "name": "name5", "value": "70", "properties": []}, {"type": "type6", "name": "name6", "value": "60", "properties": []}, {"type": "type7", "name": "name7", "value": "2", "properties": []}, {"type": "type8", "name": "name8", "value": "7", "properties": []}]}}
  </code></pre>