How do I grab this nested record set

[bsteiger]

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

client_id:84877
vat_number:null
notes:"{"blocks":[{"key":"ar0jl","text":"Marketing retainer-Chris. The app—new PM","type":"unstyled","depth":0,"inlineStyleRanges":[],"entityRanges":[],"data":{}}],"entityMap":{}}"
created_by:387140
updated_by:353118

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.

[minijohn]

Can you share your query and it's output?

[bsteiger]

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.

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

order by client ASC

[minijohn]

Thanks, so if the output is an object try:

j_clients.data.notes.blocks[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.

[bsteiger]

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

and

[Kabirdas]

Hmm it looks like t_client.selectedRow.data.notes is a string. Can you try JSON.parse(t_client.selectedRow.data.notes).blocks[0].text ?

[bsteiger]

@Kabirdas

JSON.parse(t_client.selectedRow.data.notes).blocks[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.

[Kabirdas]

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 = {{t_client.selectedRow.data.notes}};
try{
  const parsedNotes = JSON.parse(notes);
  return parsedNotes.blocks[0].text;
}catch{
  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?