I have items (users) in a PostgeSQL database with JSON data
field with such structure:
{
"department": [
"IT"
],
"competencies": [
"moderator",
"language_ru",
"language_en"
]
}
Database is accessed using flask-sqlalchemy.
from sqlalchemy.dialects.postgresql import JSON
class UserDBO(db.Model):
__tablename__ = 'user'
id = db.Column(db.BigInteger, primary_key=True)
display_name = db.Column(db.String(length=100), index=True),
email = db.Column(db.String(length=254), index=True)
phone = db.Column(db.String(length=16), index=True)
created = db.Column(db.DateTime)
data = db.Column(JSON, default={}, nullable=False)
I need to select users that have certain competencies. I had tried the following filters for sqlalchemy query (none worked as expected, most return errors):
from sqlalchemy.types import Unicode, String
from sqlalchemy import cast
from sqlalchemy import func
from sqlalchemy.dialects import postgresql
records = UserDBO.query.filter(
# func.json_contains(UserDBO.data['competencies'],cast('moderator', String)),
# UserDBO.data['competencies'].contains({'competencies':['moderator']}),
# UserDBO.data['competencies'].contains('moderator')
# UserDBO.data['competencies'].astext.cast(Unicode) == 'moderator',
# UserDBO.data['competencies'] == ['moderator'],
# ['moderator'] in UserDBO.data['competencies'],
# ['moderator'] in UserDBO.data['competencies'],
# UserDBO.data['competencies'] == cast('moderator', Unicode),
)
I need to select items that match several competencies like ['moderator','language_en'] in UserDBO.data['competencies']
.
Selecting them one by one using some filter like 'moderator' in UserDBO.data['competencies']
for one competency is also OK.
Also it will be very interesting to optimize such query by creating appropriate indeces.