How do I grab this nested record set

Inside of my query, I have this dataset and I'm looking to grab the text inside of notes:

notes:"{"blocks":[{"key":"ar0jl","text":"Marketing retainer-Chris. The app—new PM","type":"unstyled","depth":0,"inlineStyleRanges":[],"entityRanges":[],"data":{}}],"entityMap":{}}"

I want to put it inside of a text field to display the notes.

Second I put to put it back into the database, is there anything I need to do differently when placing it back. I'm going to use an update method.

Can you share your query and it's output?

Here's the query:

j_clients (Query JSON with SQL)
it pulls directly from an API and then I'm joining it with a Google Sheet. The Google Sheet is super simple but most of the data is coming directly from the API.

  gs.client_id, as gam, gs.rm as grm, as gpm, as active, as client, as industryname, as industryid
	{{}} as gs
  left join {{}} as c on gs.client_id =
  left join {{}} as ind on gs.industry =
where is not null
	and ({{!am_filter.value}} or = {{am_filter.value}})
	and ({{!pm_filter.value}} or = {{pm_filter.value}})
  and ({{!rm_filter.value}} or gs.rm = {{rm_filter.value}})
  and ({{!industry_filter.value}} or = {{industry_filter.value}})
  and ({{!hide_inactive_clients.value}} = true or = true)
  and ({{!hide_hosting_clients.value}} = true or gs.hosting_only = false)
  and ({{!show_missing_managers.value}} = true or IS NULL or ='' or IS NULL or ='' or gs.rm IS NULL or gs.rm ='')
  and ({{!client_name_search.value}} or lower( ilike {{ '%' + client_name_search.value + '%' }})

order by client ASC

Thanks, so if the output is an object try:[0].text

If you need to transform it into an object you can use formatDataAsObject .

Everything looks right to me, you just need to figure out the exact syntax to get to your notes text.

I think I'm getting close - i've tried with both the FormatDataasObject and Array and still nothing.

Screen Shot 2022-01-11 at 1.21.02 PM


Hmm :thinking: it looks like is a string. Can you try JSON.parse([0].text ?

This worked!! However, I'm baffled by something I see coming out of this API, and I'm not sure how to test for this.

So you'll see in the below screenshot, this note is NOT in a string, its just content.

How would I test to pull this and if content is in string I pull that.

You might want a dedicated transformer for this so you can run a try..catch that will see if the notes property contains proper JSON. Something like this:

const notes = {{}};
  const parsedNotes = JSON.parse(notes);
  return parsedNotes.blocks[0].text;
  return notes;

Then you can just reference {{ yourTransformerName.value }}. When the notes are nested in an object are they consistently nested with the same structure?

1 Like