Select data from JSON in nested data

Queries and Resources
1

Is there a way to display only some data from a nested property in a JSON SQL query? Such as:

[{
  // name of user
  name: 'Sarah',
  age: 24,
  /* list of roles user owns
  note that user can have multiple roles */
  roles: [ 
    { id: 1, name: 'Engineer' },
    { id: 2, name: 'Admin' },
  ],
}]

To appear in a Table like:

Name | Age | Roles
Sarah | 24 | Engineer, Admin

Cheers

2

Hey @NickJ, welcome to the community :hugs:

Couldn't get it to work with AlaSQL (which is what the "Query JSON with SQL" query uses). Try a transformer in the meantime:

const output = data.map(x => {
  return {
    name: x.name, 
    age: x.age,
    roles: x.roles.map(x => {return x.name}).join(", ")
  }
})

return output
1 Like
3

That solution was awesome @minijohn

The transformers did the trick and enabled me to tailor my data in the rest queries before combining them in my SQL query.

Thanks for you help!!!

1 Like
4