flatten array of maps to create tsv format

[ckuenne]

i'm trying to collapse/flatten a structure so i can convert to tsv format.

Version of yq: 4.45.1
Operating system: Debian 11
Installed via: wget binary release

test.yml:

- name: Miller, Tim
  email: [email protected]
  user_metadata:
    username: tmiller
    x: 1
- name: Jones, Karen
  email: [email protected]
  user_metadata:
    username: kjones
    y: 2

this works as long as i ignore the map "user_metadata":

yq -o tsv '.[] |= pick(["name","email"])' test.yml
->
name	email
Miller, Tim	[email protected]
Jones, Karen	[email protected]

but i also need to flatten the map inside the array (basically remove the first level) and then select from the remaining keys. this is my desired output:

name	email	username
Miller, Tim	[email protected]	tmiller
Jones, Karen	[email protected]	kjones

it's probably not difficult but i'm out of ideas...