1879039 - Nested repeated key-value arrays don't translate nicely to JSON

Status: RESOLVED FIXED

(Data Platform and Tools :: Glean Platform, enhancement, P1)

Description

[Jan-Erik Rediger [:janerik]]

Right now we encode the metrics as JSON into its own column.
Everything in there is now accessible.

However e.g. labeled counters show up as this:

{"glean_error_invalid_overflow":[{"key":"page.referrer","value":1},{"key":"glean.page_load","value":2}]}

It's possible to turn them into something nicer with a (hidden) UDF:

mozfun.json.from_map_json(JSON_EXTRACT(metrics, '$.labeled_counter.glean_error_invalid_overflow'))

That gives us

{"glean.page_load":2,"page.referrer":1}

It would be nice if that would be the default.
We would need a new UDF that applies from_map_json to things nested within $.labeled_counter

Comment 1

[Jan-Erik Rediger [:janerik]]

CREATE TEMP FUNCTION nested_from_map_json(input JSON)
RETURNS JSON
LANGUAGE js
AS
  """
  if (input && Object.keys(input).length) {
    for (const k in input) {
      input[k] = input[k].reduce((acc, {key, value}) => {
        acc[key] = value;
        return acc;
      }, {});
    }
    return input;
  }
  return null;
""";

Comment 2

[BMO Github Automation]

Attachment: [mozilla/bigquery-etl] Bug 1879039 - events stream: Convert nested maps in metrics (#4981)

Comment 3

[Jan-Erik Rediger [:janerik]]

badboy merged PR [mozilla/bigquery-etl]: Bug 1879039 - events stream: Convert nested maps in metrics (#4981) in 6d8a3d2.