flip off the "hashable" flag for PG unhashable types: JSON, JSONB (already has it), ARRAY. also NULLTYPE, add/test type_coerce as well as cast() to indexed access

[Joey Pereira]

There is an exception while doing a query on a declarative base model where you add a column that evaluates to a JSON value and you have used deferred loading

The following code

from sqlalchemy.dialects.postgresql import JSONB, BIGINT
base = declarative_base()
class ModelA(base):
    id = Column(BIGINT, primary_key=True)

class ModelB(base):
    id = Column(BIGINT, primary_key=True)
    jsoncol = Column(JSONB)

session.query(ModelA) \
.filter(ModelA.id.in_(ids)) \
.options(Load(ModelA).load_only("id")) \
.join(ModelB, ModelB.id == ModelA.id) \
.add_columns([ModelB.jsoncol['jsonfield'].label('jsonfield')]) \
.all()

ModelB will look something like

id = 1
jsoncol = {
  'jsonfield': {
    'k1': 1,
    'k2': 2
  }
}

# in this case
ModelB.jsoncol['jsonfield']
=>  {
  'k1': 1,
  'k2': 2
}

Causes the exception

  File "/home/piinpoint/.virtualenv/piinpoint/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2399, in all
    return list(self)
  File "/home/piinpoint/.virtualenv/piinpoint/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/home/piinpoint/.virtualenv/piinpoint/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/piinpoint/.virtualenv/piinpoint/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 75, in instances
    rows = util.unique_list(rows, filter_fn)
  File "/home/piinpoint/.virtualenv/piinpoint/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 756, in unique_list
    if hashfunc(x) not in seen
TypeError: unhashable type: 'dict'

x
=> sqlalchemy.util._collections.result((<ModelA object at 0x7f8088868710>, {u'19': 100935, u'24': 103674, u'14': 98276}))

hashfunc(x)
=> (140190023059216, {u'19': 100935, u'24': 103674, u'14': 98276})

Currently my workaround is to do cast(jsonexpression, TEXT)

[Mike Bayer]

let me make that simpler. any query of the form query(Entity, Entity.some_unhashable_type) is going to cause this:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSON

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(JSON)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)
s.add(A(data={"x": "y"}))
s.commit()

print s.query(A, A.data).first()

this is why the PG ARRAY type has an argument as_tuple to deal with this.

There's also a "hashable" flag that we should be using here (and for ARRAY) so I think we should just flip that on.

For now here is your workaround:

json_type = JSONB()
json_type.hashable = False

class ModelB(base):
    id = Column(BIGINT, primary_key=True)
    jsoncol = Column(json_type)

[Mike Bayer]

ah. you need more of a workaround, one moment

[Mike Bayer]

so the return value of the indexed thing is NullType. We don't know what the index of a JSON type returns. As part of this issue we will add hashable=False to NULLTYPE as well. For now you need to type_coerce:

type_coerce(A.data['something'], json_type)

the JSONB type apparently already has the "hashable=False" flag, and JSON does not. It's pretty broken.

[Joey Pereira]

Ah thanks so much! type_coerce is exactly what I was looking for as I tried to get your previous workaround to work for this case :)

I'll be sure to try to make some PR's for the JSON class soon :)!

Notably the ColumnClause returned from the JSON types __getitem__ needs to always have type_coerce correct?

(with respect to https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/json.py#L188 )

[Joey Pereira]

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/json.py#L52 result_type should default to JSON I think :)

[Mike Bayer]

with these PG types there's the whole issue of not just the type itself, but what does someobject['foo']['bar'] return. In #3487, we sort of know it for an ARRAY type. But for a JSON, we don't. There's a "cast" operator though directly on that object for this purpose, I'd propose adding a type_coerce also...so! right now you can also do: A.data['something'].cast(json_type).

[Joey Pereira]

You also mentioned NULLTYPE. By that do you mean to add hashable=False also to NULLTYPE? I've changed the rest in a PR

What do you mean by test type_coerce and indexed access? So far what I've done in a PR( https://bitbucket.org/zzzeek/sqlalchemy/pull-request/57/postgresql-json-and-array-modifications/diff ) is make it so someobject['foo']['bar'] works as a return_type is passed all the way through to BinaryExpression so I think that will work just fine :)

[Mike Bayer]

in https://bitbucket.org/zzzeek/sqlalchemy/src/39adada25d71ac33d2a468cc19eb61c0fdfa9280/?at=ticket_3499 this is ready to go, my understanding of PG JSON has improved such that I now know that all return values from a JSON are still JSON, or TEXT if you use the ->> operator, even if you access a single scalar value. so we do know the type in all cases, it's JSON or TEXT and that's it.

[Mike Bayer]

• add tests for hashable flag, references #3499

→ <<cset f657d4283b74>>

[Mike Bayer]

• merge of ticket_3499 indexed access branch
• The "hashable" flag on special datatypes such as :class:.postgresql.ARRAY, :class:.postgresql.JSON and :class:.postgresql.HSTORE is now set to False, which allows these types to be fetchable in ORM queries that include entities within the row. fixes #3499
• The Postgresql :class:.postgresql.ARRAY type now supports multidimensional indexed access, e.g. expressions such as somecol[5][6] without any need for explicit casts or type coercions, provided that the :paramref:.postgresql.ARRAY.dimensions parameter is set to the desired number of dimensions. fixes #3487
• The return type for the :class:.postgresql.JSON and :class:.postgresql.JSONB when using indexed access has been fixed to work like Postgresql itself, and returns an expression that itself is of type :class:.postgresql.JSON or :class:.postgresql.JSONB. Previously, the accessor would return :class:.NullType which disallowed subsequent JSON-like operators to be used. part of fixes #3503
• The :class:.postgresql.JSON, :class:.postgresql.JSONB and :class:.postgresql.HSTORE datatypes now allow full control over the return type from an indexed textual access operation, either column[someindex].astext for a JSON type or column[someindex] for an HSTORE type, via the :paramref:.postgresql.JSON.astext_type and :paramref:.postgresql.HSTORE.text_type parameters. also part of fixes #3503
• The :attr:.postgresql.JSON.Comparator.astext modifier no longer calls upon :meth:.ColumnElement.cast implicitly, as PG's JSON/JSONB types allow cross-casting between each other as well. Code that makes use of :meth:.ColumnElement.cast on JSON indexed access, e.g. col[someindex].cast(Integer), will need to be changed to call :attr:.postgresql.JSON.Comparator.astext explicitly. This is part of the refactor in references #3503 for consistency in operator use.

→ <<cset ceeb033054f0>>