Curate nested JSON fields in dataset columns

  • Updated

Curate the nested fields inside a JSON column to turn buried properties into query-ready columns with readable names. Analytics stores structured data in a JSON column as a single object. A nested field is one property inside that object. A curated field behaves like any other column in an exploration, the analyses you build in Analytics. A disabled field stays hidden in the terminal select and in every exploration that uses the dataset..

Expand a JSON column

Nested fields load on demand, so the Column Properties tab stays responsive for columns with many properties.

  1. Go to Data > Datasets.

  2. Click the dataset you want to curate.

  3. Click the Column Properties tab.

  4. Click the Refresh icon for the Json field to refresh the JSON index. See Refresh the JSON index.

  5. Click Expand on a column with the Json data type to load its nested fields as child rows.

  6. Expand a nested object field to reach the next level. Nesting has no fixed limit. Keep expanding until you reach the field you want.

You can only expand Json data type columns. You have to save the dataset after you add a column before you can expand it.

Two columns identify each nested field. Sql Definition shows the full path to the field, such as a key.b key.ckey. Data Type shows the type Analytics detected, such as Int64 or String. A field that is itself an object keeps the Json type and expands further.

Refresh the JSON index

Analytics discovers nested fields from a JSON index. A column with a missing or stale index displays an empty state with a rebuild action instead of an error. Click Refresh JSON index on the column you want to rebuild.

Analytics rebuilds the index in place. The refresh preserves the curation you already applied. Disabled fields stay disabled, and measure or attribute classifications stay in place.

Nested rows collapse when the index refreshes. Expand the JSON field again to see the updated field list.

Curate nested fields

Each nested field carries the same properties as a top-level column. Sql Definition, Data Type, and Data Index are read-only for nested fields. You can edit the rest inline:

  • Name – The display name of the field in explorations. Edit it to give a cryptic key a readable label.
  • Sql Definition – The dotted key path to the field, such as akey.bkey.ckey. This value is read-only.
  • Description – Text that explains the purpose or contents of the field.
  • Categories – Labels that group the field with related entities for filtering and discovery.
  • Data Type – The data type Analytics detected for the field. This value is read-only.
  • Measure or Attribute – Sets how Analytics treats the field. Choose measure for analysis, or attribute for grouping and filtering.
  • Def. Agg. (default aggregation) – The operation Analytics applies by default when an exploration uses the field.
  • Data Index – Caches the distinct values of the field so value drop-down lists populate faster. This value is enabled for nested fields and read-only.

  • Enabled – Controls whether the field is visible and usable in analyses.

Click a value to edit it. Click Save to apply the changes.

Enable or disable nested fields

You can disable the fields you do not use. A disabled field drops out of terminal select and stays in the dataset, so nothing is deleted. You can toggle a single field with its Enabled control.

To change every descendant of a top-level JSON column at once, follow these steps:

  1. Open the Enabled actions menu on the JSON column row.
  2. Click Enable for all child rows or Disable for all child rows.

  3. Click Save to apply the changes.

Use category filters in an exploration

The terminal select is the field picker you use when you build an exploration. A large JSON column adds many fields to it. You can use categories narrow the list, so you can reach the right field without scrolling every nested property.

  1. Click the Column Properties tab.

  2. Click Assign categories and select category for each field you want to group. You can select multiple categories for a field.

  3. Open the exploration where you want to use the fields.

  4. Filter the terminal select by the category you assigned.

To use a category that does not exist yet:

  1. Go to Data > Datasets.
  2. Select a dataset and click the Column Properties tab.

  3. Click Assign categories > Add New Category for the field you want to categorize.

  4. Enter the Name.
  5. Optionally, add the Description, Color, Icon, and select the users and groups that can edit and apply the category.

  6. Click Add category.

Only enabled fields display in the terminal select. A disabled nested field stays hidden whatever category it carries, so enable the field before you categorize it.

Categories also filter the datasets page and the home page. One label applied during curation helps your team find the dataset and the fields inside it. For category permissions and visibility rules, go to Categories.

Troubleshoot missing nested fields

Use the following symptoms to identify the cause:

  • A Json column shows no child rows – The index is missing or stale. Click Refresh JSON index on that column.
  • A known property is absent from the field list – The property falls outside the Index Time Range on the Semantics tab, or it arrived after the last index build. Widen the range, then refresh the index.
  • A curated field is unavailable in an exploration – The field is disabled. Enable it in the Column Properties tab.