Hacker News Reader

Comment by oneweekwonder | Hacker News Reader

To django model users. Can you query keys inside a JSON datatype(pgsql)[0] from the ORM?

For example in sqla you will do:

    records = db_session.query(Resource).filter(
               Resources.data["lastname"].astext == "Doe"
           ).all()

with data being the json column and lastname a key in the column.

[0]: https://stackoverflow.com/a/29975187

View on HN

Django's version of that query (to me) looks a little neater too.

    records = Resource.objects.filter(
               data__lastname__exact="Doe"
           )

One can also write the sqla query as:

    Resource.query.filter(
        Resources.data["lastname"].astext=="Doe"
    )

Personally I find the "__" convension strange and not sure that it would be obvious to new comers that you are accessing a key in the data column.

Sure you can. See the documentation: https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/f...

Thank for the quick reply. Now I need to understand how the "__" magic work; And I wonder what would django do if my key is "animal__breed".

Will it understand:

    Dog.objects.filter(data__animal__breed='collie')

But this is maybe questions I should rather ask on #django or stackoverflow.

You probably simply can't do a `data['animal__breed']` query (not with that syntax anyway; you can implement custom lookups though) if your key happens to contain a double underscore.

Django won't let you name a model field with a double underscore, I believe. Not sure how this would work with a Postgres JSON type column though, but it would probably throw an error.

Reply on news.ycombinator.com