User Postgres json operator #>> in Peewee-CodePudding

I'm trying to use the json #>> text[] → text operator on my Postgres database through peewee, as documented through https://www.postgresql.org/docs/current/functions-json.html. For example, running the query select * from table where UPPER(data #>> '{a}') = UPPER('search_string')

I seem to be having some trouble however getting this to work. I have tried using the raw where = SQL("UPPER(%s #>> '{a}') = UPPER(%s)", MyTable.data, search_string)). Though am running into a number of errors here (SQL can only take one format parameter it seems. I've tried weird work-arounds but it also doesn't seem to be happy parsing '{a}'). I've also tried writing it as a peewee Expression, though can't seem to get the desired output either. Writing the code below seems to try to cast '{a}' to json first in the finalized Postgres query, which results in an error.

def text_equals(lhs, rhs):
    return Expression(lhs, '#>>', rhs)
text_equals(MyTable.data, '{a}')

Any advice would be greatly appreciated!!!

CodePudding user response：

You can use the BinaryJSONField.path method.

Here is an example model w/a json field named "data":

class KV(Model):
    key = CharField(unique=True)
    data = BinaryJSONField()
    class Meta:
        database = db

db.create_tables([KV])

# Create some data with nested objects.
KV.create(key='k1', data={'root': {'c': {'gc1': 'gcv1-1'}}})
KV.create(key='k2', data={'root': {'c': {'gc1': 'gcv2-1'}}})

query = KV.select().where(
    fn.UPPER(KV.data.path('root', 'c', 'gc1')) == fn.UPPER('gcv2-1'))
for row in query:
    print(row.key)  # Prints "k2"

We can also select the value by putting the call to .path() in the .select() method:

query = KV.select(KV.key, KV.data.path('root', 'c', 'gc1').alias('gc1'))
for row in query:
    print(row.key, row.gc1)

# Prints
# k1 gcv1-1
# k2 gcv2-1

CodePudding user response：

In your example the '{a}' seems to be fixed and the paramater (bind variable) is the search string.

So if everything else fails you may define a view that precalculates the json extraction and query the view instead of the original table

Example

create view MyView as
select 
   data,
   upper(data #>> '{a}') search_string
from MyTable;


select data 
from MyView
where search_string = 'YY';

If you are able to pass a query to your ORM, you may even use subquery instead of an explicite view

with subq as (
select 
   data,
   upper(data #>> '{a}') search_string
from MyTable)
select data 
from subq
where search_string = 'YY'