Problems reading data through API - columns with Text, List and Record types

[kemppaik]

I'm trying to pull data in through an API. I have been able to successfully connect but having an issue getting at some of the actual content of the data we're looking at.

There are two columns giving us problems: Content and Value

Some of the values in these columns are text others are JSON and come back as List or Records.

I need to get at the values in the list (each of the lists are dynamic, not static).

In this first row value, I need to get the FirstName and Last Name for the "Observer Name" field. When I copy the Value column and transform it JSON, it's a List. When I click on the List it's a "Record" and then when I click on the Record can I see the details.

When I expand the List for Observer Name (the first row in the screenshot above) and then make the Record a table I can get these details:

For the second row, ("Select Client" row), the record contains a list.

When I click on the List and then convert the results to a Table and expand it, I get these results.

I then need to filter the list to return the selected = TRUE results.

How can I dynamically accomplish this for all of the different data types in a single column and for all of the different types of content that is available?

Thanks in advance.