Google App Engine Datastore - query with StructuredProperty in projection and filter

Question

I have a couple ndb models looks like these:

class Product(ndb.Model):
    manufacturer = ndb.StringProperty()
    category = ndb.StringProperty()
    price = ndb.FloatProperty()
class Customer(ndb.Model):
    customerId = ndb.StringProperty()
    name = ndb.StringProperty()    
    products = ndb.StructuredProperty(Product, repeated=True)

And I'd like to query based on 'manufacturer' and 'category' of the product he/she owns. So this query works as expected.

query = Customer.query(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"],
                                                    category=data_json["product"]["category"]))

results= query.fetch()

However, I cannot get the "projection" to work along with this query. The following query simply returned nothing.

query = Customer.query(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"],
                                                    category=data_json["product"]["category"]))

results= query.fetch(projection=[Customer.products.price])

But if I use the projection without the filter, the projection part works fine. The following query will return all entities but only the 'price' property

results= Customer.query().fetch(projection=[Customer.products.price])

Any thoughts? Thanks.

BTW, my queries were developed based on this article. https://cloud.google.com/appengine/docs/standard/python/ndb/queries#filtering_structured_properties

Do you have a composite index for such query? It might not always be needed, being an equality-only filter, see stackoverflow.com/questions/48896357/…. But from what I read in the article mentioned in the comments I suspect sometimes it might not work without one. To force a complaint about the index and maybe get a very close index definition add temporarily an ordering of the results. Then modify the definition to remove the direction: in it (and the result ordering). — Dan Cornilescu
Hi Dan, thanks for the feedback. I do have composite index by checking the index.yaml. Maybe I missed something, but I am curious why would the index matters in this case? The query without the projection was able to return the expected results with equality filter, but just not with project. Thank you. — James
Never mind then - if you have the index my theory doesn't apply. — Dan Cornilescu

dsesto dsesto · Accepted Answer · 2018-03-20T12:00:02

The correct way of combining AND and OR operations in the ndb library is documented in NDB Client Library's documentation.

With the query below, you are performing an AND operation in the filter, so instead of this one, you should use the one I propose below, using ndb.AND().

# Your query
query = Customer.query(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"], category=data_json["product"]["category"]))

# Query using ndb.AND
query = Customer.query(ndb.AND(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"]), Customer.products == Product(category=data_json["product"]["category"])))

Also, it turns out that if you perform the filtering in multiple steps, the query also works:

# Your request
query = Customer.query(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"], category=data_json["product"]["category"]))
results = query.fetch(projection=[Customer.products.price])

# Request performing filter in multiple steps
query = Customer.query(Customer.products == Product(category=data_json["product"]["category"]))
query1 = query.filter(Customer.products == Product(manufacturer=data_json["product"]["manufacturer"]))
results = query1.fetch(projection=[Customer.products.price])

You can use either of the proposed alternatives, although I would suggest using ndb.AND() as it minimizes the code and is also the best way to combine AND operations.

UPDATE with some code:

app.yaml

runtime: python27
api_version: 1
threadsafe: true

handlers:
- url: /.*
  script: main.app

main.py

import webapp2
from google.appengine.ext import ndb

# Datastore Models
class Product(ndb.Model):
    manufacturer = ndb.StringProperty()
    category = ndb.StringProperty()
    price = ndb.FloatProperty()

class Customer(ndb.Model):
    customerId = ndb.StringProperty()
    name = ndb.StringProperty()
    products = ndb.StructuredProperty(Product, repeated=True)

# Create entities for testing purposes
class CreateEntities(webapp2.RequestHandler):
    def get(self):
        prod1 = Product(manufacturer="Google", category="GCP", price=105.55)
        prod2 = Product(manufacturer="Google", category="GCP", price=123.45)
        prod3 = Product(manufacturer="Google", category="Drive", price=10.38)
        prod1.put()
        prod2.put()
        prod3.put()

        cust1 = Customer(customerId="Customer1", name="Someone", products=[prod1,prod2,prod3])
        cust2 = Customer(customerId="Customer2", name="Someone else", products=[prod1])
        cust3 = Customer(customerId="Customer3", name="Noone", products=[prod3])
        cust1.put()
        cust2.put()
        cust3.put()

        # Response text
        self.response.headers['Content-Type'] = 'text/plain'
        self.response.write('Done creating entities')

class GetEntities(webapp2.RequestHandler):
    def get(self):
        # This will not work
        #query = Customer.query(Customer.products == Product(category="GCP", manufacturer="Google"))
        #results = query.fetch(projection=[Customer.products.price])

        # Alternative 1 - WORKS
        #query = Customer.query(Customer.products == Product(category="GCP"))
        #query1 = query.filter(Customer.products == Product(manufacturer="Google"))
        #results = query1.fetch(projection=[Customer.products.price])

        # Alternative 2 - WORKS
        query = Customer.query(ndb.AND(Customer.products == Product(manufacturer="Google"), Customer.products == Product(category="GCP")))
        results = query.fetch(projection=[Customer.products.price])

        self.response.out.write('<html><body>')
        for result in results:
            self.response.out.write("%s<br><br>" % result)
        self.response.out.write('</body></html>')

app = webapp2.WSGIApplication([
    ('/createEntities', CreateEntities),
    ('/getEntities', GetEntities),
], debug=True)

Google App Engine Datastore - query with StructuredProperty in projection and filter

1 Answers