cal-itp/data-infra

Benefits: fact table expanding keys from JSON columns

Closed this issue · 1 comments

We want to expand the JSON from Fct Benefits Events columns into regular columns to make it easier to build a further refined set of tables. This will likely be wide and sparse (many columns, with many blanks across various rows).

Acceptance criteria

The following JSON columns are expanded, with all possible keys created as new columns in a single table:

  • event_properties
    E.g.
    {
      "eligibility_types": ["courtesy_card"],
      "eligibility_verifier": "MST Courtesy Card Eligibility Server Verifier",
      "path": "/eligibility/",
      "transit_agency": "Monterey-Salinas Transit"
    }
  • user_properties
    E.g.
    {
      "referrer": "http://benefits.calitp.org",
      "referring_domain": "benefits.calitp.org",
      "user_agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.137 Safari/4E423F"
    }

@allejo will take this one