Step 6: Create data queries - AWS Prescriptive Guidance

Step 6: Create data queries

After you define your access patterns and design your data model, you can query hierarchical data in the DynamoDB database. As a best practice to save on costs and help ensure performance, the following examples use only the query operation without Scan.

  • Find ancestors of a component.

    To find the ancestors (parent, grandparent, great-grandparent, and so on) of the CM8 component, query the base table using ComponentId = "CM8". The query will return the following record.

    To reduce the size of the result data, you can use a projection expression to return only the Path attribute.

    ComponentId

    ParentId

    GraphId

    Path

    CM8

    CM4

    CM1#1

    CM1|CM2|CM4|CM8

    Path

    CM1|CM2|CM4|CM8

    Now, split the path using the pipe ("|"), and take the first N-1 components to get ancestors.

    Query result: The ancestors of CM8 are CM1, CM2, CM4.

  • Find immediate children of a component.

    To get all immediate child, or one-level downstream, components for the CM2 component, query GSI1 using ParentId = "CM2". The query will return the following record.

    ParentId

    ComponentId

    CM2

    CM4

    CM5

  • Find all downstream child components using a top-level component.

    To get all child, or downstream, components for top-level component CM1, query GSI2 using GraphId = "CM1#1" and begins_with("Path", "CM1|"), and use a projection expression with ComponentId. It will return all the components related to that tree.

    This example has a single tree, with CM1 as the top component. In reality, you could have millions of top-level components in the same table.

    GraphId

    ComponentId

     

    CM1#1

    CM2

    CM3

    CM4

    CM5

    CM8

    CM9

    CM10

    CM6

    CM7

  • Find all downstream child components using a middle-level component.

    To get all child, or downstream, components recursively for component CM2, you have two options. You can query recursively level by level, or you can query the GSI2 index.

    • Query GSI1, level by level, recursively, until reaching the last level of child components.

      1. Query GSI1 using ParentId = "CM2". It will return the following record.

        ParentId

        ComponentId

        CM2

        CM4

        CM5

      2. Again, query GSI1 using ParentId = "CM4". It will return the following record.

        ParentId

        ComponentId

        CM4

        CM8

        CM9

      3. Again, query GSI1 using ParentId = "CM5". It will return the following record.

        Continue the loop: Query for each ComponentId until you reach the last level. When a query using ParentId = "<ComponentId>" doesn't return any results, the previous result was from the last level of the tree.

        ParentId

        ComponentId

        CM5

        CM10

      4. Merge all results.

         

        result=[CM4, CM5] + [CM8, CM9] + [CM10]

                 =[CM4, CM5, CM8, CM9, CM10]

    • Query GSI2, which stores a hierarchical tree for a top-level component (a car, or CM1).

      1. First, find the top-level component or top ancestor and Path of CM2. For that, query the base table by using ComponentId = "CM2" to find the path of that component in the hierarchical tree. Select the GraphId and Path attributes. The query will return the following record.

        GraphId

        Path

        CM1#1

        CM1|CM2

      2. Query GSI2 by using GraphId = "CM1#1" AND BEGINS_WITH("Path", "CM1|CM2|"). The query will return the following results.

        GraphId

        Path

        ComponentId

        CM1#1

        CM1|CM2|CM4

        CM1|CM2|CM5

        CM1|CM2|CM4|CM8

        CM1|CM2|CM4|CM9

        CM1|CM2|CM5|CM10

        CM4

        CM5

        CM8

        CM9

        CM10

      3. Select the ComponentId attribute to return all the child components for CM2.