Access json which has no key in sqlalchemy

user_13511372 0
Viewed 530

I have a column "value" with type postgres 'JSON'. Example data in the value column.

  1. "displayname1"
  2. {"middleName": "other"}
  3. [{"primary": true, "type": "work", "value": "rusr@r.com"}, {"primary": true, "type": "work", "value": "iu@n.com"}]

I know i can access json with key-value pairs like this,

.filter(Values.value['middleName'].astext=='other')(values is model name)

the question is how do you access string value or array of objects.

i tried,

.filter(Values.value.astext=='displayname1') it gives me AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Values.value has an attribute 'astext'

and

.filter(Values.value['type'].astext=='work')- here i am trying filter "type":"work" objects in the array. How do you do this. Is it possible to search in all objects with out specifying the index.

I am using postgres as backend Db.

Models -

class Values(db.Model):
    __tablename__ = 'attribute_values'
                    
    id = db.Column(db.Integer, primary_key=True)
    attribute = db.Column(db.Integer, ForeignKey('attributes.id'), primary_key=True)
    value = db.Column(JSON)

class Attributes(db.Model):
    __tablename__ = 'attributes'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)

   user_attribute = db.relationship(Values, backref='attributes', primaryjoin=id == Values.attribute)

query that i am trying -

db.session.query(Values).join(Attributes, Values.attribute == Attributes.id).filter("what filters that i should be using for the cases mentioned above").all()
sqlalchemy postgresql python
3 Answers

Assuming the json object is a string, use json.loads to convert it into a dictionary then index it normally.

>>> import json
>>> s = """[{"primary": true, "type": "work", "value": "rusr@r.com"}, {"primary": true, "type": "work", "value": "iu@n.com"}]"""
>>> my_dict = json.loads(s)
>>> my_dict[0]['type']
'work'

Then you can search for any lines where type == work using the following list comprehension.

>>> search = [i for i in my_dict if i['type'] == 'work']
>>> search
[{'primary': True, 'type': 'work', 'value': 'rusr@r.com'}, {'primary': True, 'type': 'work', 'value': 'iu@n.com'}]

Here is an example of the same thing without using a list comprehension.

>>> search = list()
>>> for i in my_dict:
...     if i['type'] == 'work':
...             search.append(i)
...
>>> search
[{'primary': True, 'type': 'work', 'value': 'rusr@r.com'}, {'primary': True, 'type': 'work', 'value': 'iu@n.com'}]
user_103773150

I can not test the code, but you can try this.

l = .filter(User_Values.value)
s = [[x for x in i if x['type'].astext=='work'] for i in l if isinstance(i, list)]

I can do testing to provide a better answer if I can be provided with the code to create the table. Sorry in advance if this isn't what you are looking for.

user_103773150

If anyone is still looking for answer, here is how its done

(cast(Values.value, db.String())=='"Employee"'))

The above is for string stored in JSONB column. Note the quotes used in that filter.

(Values.value.contains([{"type":"examplecom"}]))

This one is for searching array of JSON inside JSONB column

user_135113720
Related