How to unnest - flatten this answer

[brunet (Ferran Brunet)]

Dear community,

I need to transform the ES response to flatten the received data, i would like to make a radar type viz but i'm not able to unnest / flatten it.

I have this answer (cleaned it a bit)...

"aggregations": {
  "subject": {
    "buckets": [
      {
        "key": "Hematology",
        "person": {
          "buckets": [
            {
              "key": "John Doe",
              "hours": {
                "value": 287.5
              }
            },
            {
              "key": "Peter Duck",
              "hours": {
                "value": 203
              }
            }
          ]
        }
      },
      {
        "key": "Immunology",
        "category": {
          "buckets": [
            {
              "key": "John Doe",
              "hours": {
                "value": 110.75
              }
            },
            {
              "key": "Peter Duck",
              "hours": {
                "value": 122.75
              }
            }
          ]
        }
      }
    ]
  }

What i need, vega needs is something flattened like this:

So I can finally achieve something like this:

What i had so far is to set "propierties" : "aggregations.subject.buckets", this is ok.
But then, using project/flatten transformation, i cannot access [hours.value] since i get undefined values. Or again, have a nested json in the new field.

Anyway,... anyone knows how is this done correctly.

So many thanks.

[brunet (Ferran Brunet)]

This is the data section:
The issue is this notation -> "category.buckets[0].key", this is working but then i can access only 1 category. If I write it like this -> "category.buckets.key", aiming to get all the array then it Shows "undefined".

"data": [
{
	"name": "table"
	"url": {
		%context%: true
		%timefield%: @timestamp
		"index": whatever*
		"body": {
			"aggs": {
				"subject": {
					"terms": {"field": "subject.keyword","size": 20},
					"aggs": {
						"category" : {
							"terms": {
								"field": "name.keyword", "size": 10
							},
							"aggs": {
								"hours": {
									"sum": {
										"field": "hours_working"
									}
								}
							}
						}
					}
				}
			},
			"size": 0
		}
	},"format": {"property": "aggregations.subject.buckets"},
	"transform":[
		{
			"type": "project",
			"fields": ["key","category.buckets[0].key", "category.buckets[0].hours.value"],
			"as": ["key", "category", "value"]
		}
	]
},
{
	"name": "keys",
	"source": "table",
	"transform": [{
		"type": "aggregate",
		"groupby": ["key"]
	}]
}

[brunet (Ferran Brunet)]

Ok, so this did the trick.

"transform": [
  {
    "type": "flatten",
    "fields": ["subject.buckets"],
    "as": ["key1"]
  },
  {
    "type": "project",
    "fields": ["key1.key", "key1.hours.value", "key"],
    "as": ["key", "value", "category"]
  }
]