Table: Display query from different documents as columns

Grafana Elasticsearch
1

I have 2 types of documents:

Doc ID 1 documents:
{
doc_id: 1,
metric: 50,
user_id: ‘bob’
},
{
doc_id: 1,
metric: 60,
user_id: ‘john’
}

Doc ID 2 documents:
{
doc_id: 2,
metric: 40,
user_id: ‘john’
},
{
doc_id: 2,
metric: 30,
user_id: ‘bob’
}

I want to group these by user_id, and display the average of the metric in different columns based on the doc_id.

So the table would look like this:

User  |  doc_id:1  |  doc_id:2

bob   |     50     |    30
john  |     60     |    40

But I cannot figure out how to setup this. I am trying to do 2 queries, first one is where doc_id=1 and the second one is where doc_id=2. Grouping these by term user_id, and tried to configure the table to different display formats but somehow I am not getting there.

Any help would be appreciated.

Thanks,
TZ