Stack Exchange
how to parse highly nested json column in my dataframe
I have a dataframe that has a column named 'blocks'. in the 'blocks' column the data looks like this
[
   {
      "block_id":"5zI",
      "elements":[
         {
            "elements":[
               {
                  "type":"user",
                  "user_id":"U03MXVDRV7W"
               },
               {
                  "text":" submitted a question!\n\nTicket Number: ",
                  "type":"text"
               },
               {
                  "text":"2217258",
                  "type":"text"
               },
               {
                  "text":"\nCompany Name: ",
                  "type":"text"
               },
               {
                  "text":"Bridges Investment Management, Inc.",
                  "type":"text"
               },
               {
                  "text":"\nSummary: ",
                  "type":"text"
               },
               {
                  "text":"1 User - Lisa / Email / Ted Bridges Calendar Invites",
                  "type":"text"
               },
               {
                  "text":"\n\n",
                  "type":"text"
               },
               {
                  "text":"Team, the past CW tickets for this issue is it was fixed on its own and nothing in ITGlue. KB articles online show to update the Registry Editor, but healthy paranoia kicks in. My notes are below: \n\n-requested permision to remote into workstation\n-Workstation: BIM-LAPTOP89\n-permission granted\n-past CW ticket (ticket #1674995)\n-no ITGlue documentation on issue\n-found Microsoft KB article: ",
                  "type":"text"
               },
               {
                  "text":"https://social.technet.microsoft.com/Forums/en-US/ed03921c-f7e9-4b64-8273-5df98fa3a8e1/calendar-meeting-requests-not-showing-until-accepteddenied?forum=outlook",
                  "type":"link",
                  "url":"https://social.technet.microsoft.com/Forums/en-US/ed03921c-f7e9-4b64-8273-5df98fa3a8e1/calendar-meeting-requests-not-showing-until-accepteddenied?forum=outlook"
               },
               {
                  "text":"\n-requesting Registry Editor is updated \n-Registry Editor steps are below:\n1. Start Registry Editor (Regedit.exe).\n2. Locate the following key in the registry\n[HKEY_CURRENT_USER\\Software\\Microsoft\\Office\\14.0\\Outlook\\Options\\General]\n3. In the Edit menu, point to New , and then click DWORD Value .\n4.Type the name of the new value as AutoProcessIdleTime, and then press ENTER.\n5. On the Edit menu, click Modify , and then type the following information:\n\nValue data: 10000\nBase: Decimal\n\n6. Click OK . Quit Registry Editor",
                  "type":"text"
               },
               {
                  "text":"\n\nThe responding engineer should click ",
                  "type":"text"
               },
               {
                  "style":{
                     "bold":true
                  },
                  "text":"Claim",
                  "type":"text"
               },
               {
                  "text":" to begin assisting. Additional details will be generated in the thread once this is claimed.",
                  "type":"text"
               }
            ],
            "type":"rich_text_section"
         }
      ],
      "type":"rich_text"
   },
   {
      "block_id":"8gm",
      "text":{
         "text":"<@U01AC7RCE9Z> clicked *Claim*",
         "type":"mrkdwn",
         "verbatim":true
      },
      "type":"section"
   }
]


here is a screenshot of the entire first row of data :

I want to be able to write a function that iterates through the entire dataframe column to extract information from 'user_id", 'Ticket Number', 'Company Name', and 'Summary'. I have never worked with this type of json nested data before and am looking for ideas of how to extract this needed information into new columns in order to use the data. Thank you!

please replace the picture by the corresponding plain text

I posted a screen shot of the column, is that what you meant?

I recommend you format your JSON next time so it is readable without using monitors 5 meters wide. I did that for you this time. Also there's no point posting all the repetitive nodes (in elements) as it adds no value. One or two would suffice

thank you, I appreciate you helping out with the edit, sorry I'm new to working with this data and asking questions on here

You can make types from each piece. For example,
struct Elements: Codable {
  var type: String
  var user_id: String
}

Then incorporate them into the larger type you are using to decode.
struct ElementsBlock: Codable {
  var elements: Elements
}

struct Block: Codable {
 var blockID: String
 var elementsBlock: ElementsBlock
 . . . //other types in block JSON
}

Then when you decode to type Block, it'll fill in all the subtypes too.

I'm not sure how to decode this

I would use an instance of JSON decoder. 'let decoder = JSONDecoder()' To decode the data I would use. 'decoder.decode(Block.self, from: whateverDataYouGot)' This assumes a data type is being passed by like an URLSession. If not, I need to know more about the implementation.
