Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to check if key is in JSON sqlachemy

I am trying to select a segment if:

  • settings dict is not null
  • Has key user_parameters in it
  • And user_parameters (dict) has key user_id in 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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}'
        )
    )
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search