Datastore best way to store aggregated nested data for better querying

Question

I am a newbie to datastore. using it for an non GAE application.

I am approaching for a better design to my use case.

Storing below nested aggregated data by flattening and storing in multiple kinds for better query support.

"DateTime": "2015-10-21 12:10:50",
"Domain": "abc.com",
"Events": [
    {
        "EventName": "visit",
        "EventCount": "188",
        "Attributes_Aggregations": [
            {
                "Name": "color",
                "Value_Aggregations": [
                    {
                        "Value": "red",
                        "Count": "188",
                        "Unique_Users": [
                            {
                                "ID": "user1",
                                "Count": "38"
                            },
                        ]
                    },
                ]
            },
        ]
    },
]

I am storing it in 5 kinds. Every kind relates with another kind as ancestor key.

Kind: Domain

domain_name - abc.com

Kind: Event

evt_name - visit
evt_count - 188
evt_datetime - 2015-10-21 12:10:50
ancestor_key - Domain abc.com

Kind: Attribute

att_name - color
evt_datetime - 2015-10-21 12:10:50
ancestor_key - Domain abc.com Event visit

Kind: AttributeValue

att_value - red
att_value_count - 108
evt_datetime - 2015-10-21 12:10:50
ancestor_key - Domain abc.com Event visit Attribute color

Kind: Users

user_id - user1
count - 38
evt_datetime - 2015-10-21 12:10:50
ancestor_key - Domain abc.com Event visit Attribute color AttributeValue red

I have added 'evt_datetime' property in all kinds as it will be the primary filter key.

I've set index to all properties to enable any property filter, however due to one inequality filter on one property restriction have made me to pause.

As you can see i can not filter both on evt_datetime and evt_count with any of (>,<,>=,<=).

Is there better way to design these schema to use multiple filter or kindless query?

This is a bit broad for stack... in any case, normally when people try to do multiple inequality, we suggested a computed property that you could use. For instance, let's say you will always want events with counts over 200, you can add a "over_200" boolean parameter, and then use an = on THAT param. In general, if you need both inequalities, the best suggestion is to query with one, then use code to filter the other — Patrice, Nov 05 '15 at 15:20

score 1 · Answer 1 · answered Nov 11 '15 at 08:58

The answer maybe is to store everything inside a single entity. Use repeated properties with StructuredProperties (or LocalStructuredProperties if you want to have a complex structure).

You don't have to query for the exact entities you are looking for. Do the query with single query and filter the results by code.

This possibility really depends on how are you storing this and how many repeated items you are planning to add (as the entity size can't exceed 1MB of storage).

Datastore best way to store aggregated nested data for better querying

1 Answers1