How to query postgresql JSONB columns?

[Daniel Kerkow]

Hi,
I am new to SQLAlchemy, doing my first steps with Flask.
I have the following model using JSONB data type in PostgreSQL.

The JSON data looks like

{'key1': 'value1', 'key2': 'value2'}

The Docs are relatively sparse regarding this topic.

How can I query the properties column for containing a specific key or key=value combination?
How can I update these?
Can I get a list of unique keys in all records?

Any help is welcome!

Daniel

class Record(Base):

    """represents single stratigraphic units"""

    # ID of corresponding site:
    site_id         = db.Column(db.Integer, db.ForeignKey('public.sites.id'))

    # depth values:
    depth           = db.Column(db.Numeric, nullable=True)
    upper_boundary  = db.Column(db.Numeric, nullable=True)
    lower_boundary  = db.Column(db.Numeric, nullable=True)

    # stratigraphic properties, represented as key/value store
    properties      = db.Column(JSONB)

[Mike Bayer]

On 4/4/15 7:22 PM, Daniel Kerkow wrote:

Hi,
I am new to SQLAlchemy, doing my first steps with Flask.
I have the following model using JSONB data type in PostgreSQL.

The JSON data looks like

{'key1': 'value1', 'key2': 'value2'}

The Docs are relatively sparse regarding this topic.

the cases you ask for are all described at :

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

How can I query the properties column for containing a specific key or key=value combination?

select([table]).where(table.c.col['key'] == 'somevalue')

How can I update these?

update([table]).values({table.c.col['key'] = 'value'})

Can I get a list of unique keys in all records?

i dunno.  Whats the Postgresql query you'd like to emit?

Any help is welcome!

Daniel

class Record(Base):

    """represents single stratigraphic units"""

    # ID of corresponding site:
    site_id         = db.Column(db.Integer, db.ForeignKey('public.sites.id'))

    # depth values:
    depth           = db.Column(db.Numeric, nullable=True)
    upper_boundary  = db.Column(db.Numeric, nullable=True)
    lower_boundary  = db.Column(db.Numeric, nullable=True)

    # stratigraphic properties, represented as key/value store
    properties      = db.Column(JSONB)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Daniel Kerkow]

2015-04-05 1:29 GMT+02:00 Mike Bayer <mik...@zzzcomputing.com>:

On 4/4/15 7:22 PM, Daniel Kerkow wrote:

Hi,
I am new to SQLAlchemy, doing my first steps with Flask.
I have the following model using JSONB data type in PostgreSQL.

The JSON data looks like

{'key1': 'value1', 'key2': 'value2'}

The Docs are relatively sparse regarding this topic.

the cases you ask for are all described at :

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

How can I query the properties column for containing a specific key or key=value combination?

select([table]).where(table.c.col['key'] == 'somevalue')

Seems I am doing something wrong here:

select(Record).where(Record.c.properties['key']  == 'value')

TypeError: '_BoundDeclarativeMeta' object is not iterable

Did I miss to import something? Any preparations?

 

How can I update these?

update([table]).values({table.c.col['key'] = 'value'})

Can I get a list of unique keys in all records?

i dunno.  Whats the Postgresql query you'd like to emit?

In raw psql, the following works:
select *
from records
where properties->>'color' = 'black';

 

Any help is welcome!

Daniel

class Record(Base):

    """represents single stratigraphic units"""

    # ID of corresponding site:
    site_id         = db.Column(db.Integer, db.ForeignKey('public.sites.id'))

    # depth values:
    depth           = db.Column(db.Numeric, nullable=True)
    upper_boundary  = db.Column(db.Numeric, nullable=True)
    lower_boundary  = db.Column(db.Numeric, nullable=True)

    # stratigraphic properties, represented as key/value store
    properties      = db.Column(JSONB)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/vk6L6152vE8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On 4/4/15 7:29 PM, Mike Bayer wrote:

On 4/4/15 7:22 PM, Daniel Kerkow wrote:

Hi,
I am new to SQLAlchemy, doing my first steps with Flask.
I have the following model using JSONB data type in PostgreSQL.

The JSON data looks like

{'key1': 'value1', 'key2': 'value2'}

The Docs are relatively sparse regarding this topic.

the cases you ask for are all described at :

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

How can I query the properties column for containing a specific key or key=value combination?

select([table]).where(table.c.col['key'] == 'somevalue')

How can I update these?

update([table]).values({table.c.col['key'] = 'value'})

sorry, like this:

update([table]).values({table.c.col['key']: 'value'})


from sqlalchemy import Table, Column, MetaData

from sqlalchemy.dialects import postgresql

m = MetaData()
t = Table('t', m, Column('x', postgresql.JSONB))

print t.update().values({t.c.x['data']: 'foo'}).compile(dialect=postgresql.dialect())

output:

UPDATE t SET x -> %(x_1)s=%(param_1)s

[Mike Bayer]

On 4/4/15 7:47 PM, Daniel Kerkow wrote:

2015-04-05 1:29 GMT+02:00 Mike Bayer <mik...@zzzcomputing.com>:

On 4/4/15 7:22 PM, Daniel Kerkow wrote:

Hi,
I am new to SQLAlchemy, doing my first steps with Flask.
I have the following model using JSONB data type in PostgreSQL.

The JSON data looks like

{'key1': 'value1', 'key2': 'value2'}

The Docs are relatively sparse regarding this topic.

the cases you ask for are all described at :

http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

How can I query the properties column for containing a specific key or key=value combination?

select([table]).where(table.c.col['key'] == 'somevalue')

Seems I am doing something wrong here:

select(Record).where(Record.c.properties['key']  == 'value')

TypeError: '_BoundDeclarativeMeta' object is not iterable

Did I miss to import something? Any preparations?

First off, if you are using the select() construct, it accepts a list of things to SELECT from, so select([Record]).

Secondly, "Record" looks a whole lot like a declarative ORM class due to the "BoundDeclarativeMeta" line; there's a difference between using table metadata and an ORM class, in this case that the ORM class doesn't use the .c. attribute.   If you are using ORM classes you'd likely want to use session.query() which is generally more appropriate.

[Daniel Kerkow]

Am 05.04.2015 01:58 schrieb "Mike Bayer" <mik...@zzzcomputing.com>:
>
>
>
> On 4/4/15 7:47 PM, Daniel Kerkow wrote:
>>
>>
>> 2015-04-05 1:29 GMT+02:00 Mike Bayer <mik...@zzzcomputing.com>:
>>>
>>>
>>>
>>> On 4/4/15 7:22 PM, Daniel Kerkow wrote:
>>>>
>>>> Hi,
>>>> I am new to SQLAlchemy, doing my first steps with Flask.
>>>> I have the following model using JSONB data type in PostgreSQL.
>>>>
>>>> The JSON data looks like
>>>>
>>>> {'key1': 'value1', 'key2': 'value2'}
>>>>
>>>> The Docs are relatively sparse regarding this topic.
>>>
>>>
>>> the cases you ask for are all described at :
>>>
>>> http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON
>>>
>>>> How can I query the properties column for containing a specific key or key=value combination?
>>>
>>>
>>> select([table]).where(table.c.col['key'] == 'somevalue')
>>
>>
>> Seems I am doing something wrong here:
>>
>> select(Record).where(Record.c.properties['key']  == 'value')
>>
>> TypeError: '_BoundDeclarativeMeta' object is not iterable
>>
>> Did I miss to import something? Any preparations?
>
>
>
> First off, if you are using the select() construct, it accepts a list of things to SELECT from, so select([Record]).
>
> Secondly, "Record" looks a whole lot like a declarative ORM class due to the "BoundDeclarativeMeta" line; there's a difference between using table metadata and an ORM class, in this case that the ORM class doesn't use the .c. attribute.   If you are using ORM classes you'd likely want to use session.query() which is generally more appropriate.
>
>

Yes, that is exactly what I am looking for. Sorry for missing that. How would that actually look like in ORM style?