I am trying to select a segment if:
settingsdict is not null- Has key
user_parametersin it - And
user_parameters(dict) has keyuser_idin it
Code below:
user_id = '100952'
select([self.db.segments]).where(
and_(
self.db.segments.c.settings.isnot(None),
cast(self.db.segments.c.settings, JSON)["user_parameters"].as_string().contains(f"'{user_id}'"),
)
)
Converting JSON to a string and searching for user_id in that string is not reliable, as it does not ensure that the captured string will be a key; it may also match a value. What is the correct way to check if user_id exists as a key in the settings['user_parameters'] dictionary?
You could use jsonb_path_exists.
I haven’t tested the example, but it should look like this:
from sqlalchemy import func
select([self.db.segments]).where(
and_(
self.db.segments.c.settings.isnot(None),
func.jsonb_path_exists(self.db.segments.c.settings, f'$.user_parameters.{user_id}')
)
)
More info in the sqlalchemy docs and json functions in postgres.
2
This should work:
from sqlalchemy import select, and_, cast
from sqlalchemy.dialects.postgresql import JSONB, jsonb_path_exists
query = select([self.db.segments]).where(
and_(
self.db.segments.c.settings.isnot(None),
jsonb_path_exists(
cast(self.db.segments.c.settings, JSONB),
f'$.user_parameters.{user_id}'
)
)
)