Handling to-many results in a tabular format

You can select all assets from a domain together with their Note attributes. Each asset may have multiple notes. When there are multiple notes, the most recent note should be ordered at the top of the list.

The TableViewConfig may look similar to the example below.

  • JSON
  • YAML
{
  "TableViewConfig": {
    "Resources": {
      "Asset": {
        "Id": { "name": "assetId" },
        "Signifier": { "name": "assetName" },
        "StringAttribute": {
          "LongExpression": { "name": "note" },
          "CreatedOn": { "name": "noteCreatedOn" },
          "Order": [ { "Field": { "name": "noteCreatedOn", "order": "DESC" } } ]
        },
        "Domain": {
          "Id": { "name": "domainId" }
        },
        "Filter": { "Field": { "name": "domainId", "operator": "EQUALS", "value": "f342423f-54fd-4643-935b-adbd9e7f5e25" } },
        "Order": [ { "Field": { "name": "assetName" } } ]
      }
    },
    "Columns": [
      { "Column": { "fieldName": "assetId" } },
      { "Column": { "fieldName": "assetName" } },
      { "Column": { "fieldName": "note" } }
    ]
  }
}
---
TableViewConfig:
  Resources:
    Asset:
      Id:
        name: "assetId"
      Signifier:
        name: "assetName"
      StringAttribute:
        LongExpression:
          name: "note"
        CreatedOn:
          name: "noteCreatedOn"
        Order:
        -
          Field:
            name: "noteCreatedOn"
            order: "DESC"
      Domain:
        Id:
          name: "domainId"
      Filter:
        Field:
          name: "domainId"
          operator: "EQUALS"
          value: "f342423f-54fd-4643-935b-adbd9e7f5e25"
      Order:
      -
        Field:
          name: "assetName"
  Columns:
  -
    Column:
      fieldName: "assetId"
  -
    Column:
      fieldName: "assetName"
  -
    Column:
      fieldName: "note"

Depending on the format requested, the results might be different. In Excel or CSV format, each asset is duplicated on a new row for each note value.

Example of Collibra asset with multiple notes, with each note on a different row in Excel export

This is similar to using SQL queries to join two tables with a one-to-many relationship. Unlike SQL, if you select an asset with two notes and three responsibilities, the asset would use three lines of the Excel table, not six, and the third row in the note column would be empty. 

JSON format, on the other hand, does not add duplicate rows to the results. Instead, it returns the first note found and discards the other notes.

"First note" is missing for "Business Asset 1" 
{
    "iTotalDisplayRecords": 3,
    "iTotalRecords": 3,
    "aaData": [
        {
            "assetId": "c20d5b39-6c5d-411b-adcb-82a1dd3851cc",
            "assetName": "Business Term 1",
            "note": "Second Note"
        },
        {
            "assetId": "1a6a8f73-43b0-4a29-84c3-baaa3467be70",
            "assetName": "Business Term 2",
            "note": "Single note on BT2"
        },
        {
            "assetId": "7329349e-0631-41a7-a740-738979d887c6",
            "assetName": "Business Term 3",
            "note": "Single Note on BT3"
        }
    ]
}

For tabular formats that do not duplicate rows, you can add the Group mapping construct to the Columns section.

  • JSON
  • YAML
{
  "TableViewConfig": {
    "Resources": {
      "Asset": {
        "Id": { "name": "assetId" },
        "Signifier": { "name": "assetName" },
        "StringAttribute": {
          "LongExpression": { "name": "note" },
          "CreatedOn": { "name": "noteCreatedOn" },
          "Order": [ { "Field": { "name": "noteCreatedOn", "order": "DESC" } } ]
        },
        "Domain": {
          "Id": { "name": "domainId" }
        },
        "Filter": { "Field": { "name": "domainId", "operator": "EQUALS", "value": "f342423f-54fd-4643-935b-adbd9e7f5e25" } },
        "Order": [ { "Field": { "name": "assetName" } } ]
      }
    },
    "Columns": [
      { "Column": { "fieldName": "assetId" } },
      { "Column": { "fieldName": "assetName" } },
      {
        "Group": {
          "name": "Notes",
          "Columns": [
            { "Column": { "fieldName": "note" } }
          ]
        }
      }
    ]
  }
}
---
TableViewConfig:
  Resources:
    Asset:
      Id:
        name: "assetId"
      Signifier:
        name: "assetName"
      StringAttribute:
        LongExpression:
          name: "note"
        CreatedOn:
          name: "noteCreatedOn"
        Order:
        -
          Field:
            name: "noteCreatedOn"
            order: "DESC"
      Domian:
        Id:
          name: "domainId"
      Filter:
        Field:
          name: "domainId"
          operator: "EQUALS"
          value: "f342423f-54fd-4643-935b-adbd9e7f5e25"
      Order:
      -
        Field:
          name: "assetName"
  Columns:
  -
    Column:
      fieldName: "assetId"
  -
    Column:
      fieldName: "assetName"
  -
    Group:
      name: "Notes"
      Columns:
      -
        Column:
          fieldName: "note"

A Group mapping allows grouping multiple results for a single parent. A Group must receive a user-defined name that will be used when formatting the results.

{
    "iTotalDisplayRecords": 3,
    "iTotalRecords": 3,
    "aaData": [
        {
            "assetId": "c20d5b39-6c5d-411b-adcb-82a1dd3851cc",
            "assetName": "Business Term 1",
            "Notes": [
                {
                    "note": "Second Note"
                },
                {
                    "note": "First note"
                }
            ]
        },
        {
            "assetId": "1a6a8f73-43b0-4a29-84c3-baaa3467be70",
            "assetName": "Business Term 2",
            "Notes": [
                {
                    "note": "Single note on BT2"
                }
            ]
        },
        {
            "assetId": "7329349e-0631-41a7-a740-738979d887c6",
            "assetName": "Business Term 3",
            "Notes": [
                {
                    "note": "Single Note on BT3"
                }
            ]
        }
    ]
}

Here are some rules about Group:

  • Group mappings cannot be nested, a Group defined within a Group is not supported.
  • All columns within a group must be related to the same parent entity.