LIKE not working for json fields in postgresql

[Leonel Câmara]

This is something that used to work for me using postgres 9.3. Right now if I have a category table where the category name is a json field if I do:

db(db.category.name.like('%Bas%')).select()

I get:

ProgrammingError: operator does not exist: json ~~ text

LINE 1: ...y.modified_by FROM category WHERE ((category.name LIKE '%Bas...

Any idea what happened?

[Anthony]

Did the DAL switch from storing JSON as plain text in a text field to using the Postgres native JSON field type? Maybe that's the problem.

Anthony

[Leonel Câmara]

I ended up doing this so I could search for specific values for keys inside the JSON.

def JSON_KEY_EXPRESSION(field, key, value_type='string'):

    db = field._db

    def op(first, second):

        return "%s->>'%s'" % (db._adapter.expand(first), db._adapter.expand(second))

    return Expression(db, op, field, key, value_type)

Then I could do:

db(JSON_KEY_EXPRESSION(db.category.name, 'pt').like('%bol%', case_sensitive=False)).select()

And it worked.

is this reasonable? I think that something like this could be inside Expression like day, month, year except you could refer to json keys.

Either way, the regular like should probably cast json columns into text so people could use the regular like if that's what they want.

I would fix it myself, but I don't feel like I understand the DAL codebase well enough to contribute to it, it needs some kind of technical documentation or even just a small "intro to DAL development" so people can more easily get into the code since it has very little in the way of comments.

[Niphlod]

IMHO it's a mess and it'll be done only for postgresql. Searching with "like" in a json field shouldn't be supported at all.

[Leonel Câmara]

But it does make sense to support searching for values stored in json, the same way you can search dates for which ones have a given year. So expression could have something like I'm doing here that would call the adapter's json_extract_path_text or something.

[Niphlod]

I'd just include (only if not ready yet) an explicit cast json to text. 

Unfortunately DAL tables - and Fields - have a huge issue with anything being "nested" (which is one of the many reasons fancy and useful features of nosql backends will always be basically a PITA to use in DAL)