Is there a way to either wildcard or exclude fields in a json_contains statement (postgres w/ sqlalchemy)?

For example, lets say one of the rows of my database has a field called MyField which has a typical json value of ...

MyField : {Store: "HomeDepot", Location: "New York"}

Now, I am doing a json contains on that with a larger json variable called larger_json...

larger_json : {Store: "HomeDepot", Location: "New York", Customer: "Bob" ... }

in sqlalchemy, I could use a MyTable.MyField.comparator.contained_by(larger_json) and in this case, that would work fine. But what if, for example, I later removed 'Location' as a field in my variable... so I still have the value in my database, but it no longer exists in larger_json:

MyField : {Store: "HomeDepot", Location: "New York"}
larger_json : {Store: "HomeDepot", Customer: "Bob" ... }

Assume that I know when this happens, i.e. I know that the database has Location but the larger_json does not. Is there a way for me to either wildcard Location, i.e. something like this...

{Store: "HomeDepot", Location: "*", Customer: "Bob" ... }

or to exclude it from the json value? Something like this?

 MyTable.MyField.exclude_fields().comparator.contained_by(larger_json)

Or is there another recommended approach for dealing with this?

CodePudding user response：

Not sure if that's what you need, but you could remove Location as a key from the values you search:

... WHERE (tab.myfield - 'Location') <@ larger_json