Failed to access JSON inner object using JSON functions

[abrahamko]

I tried to construct a query that accesses inner object inside a json. So if I have a JSONB column called data with the value:

{
  "array_key": ["value1", "value2", "value3"]
}

I want to filter it using SQL query like this:

data->'array_key'@>'["value2"]'

Sadly, trying the following fails:

...
    .where(table.data.get_json_value('array_key').contains('["value2"]'))

I managed to work around it by using the implementation of contains:

    .where(BasicCriterion(JSONOperators.CONTAINS, table.data.get_json_value('array_key'), JSON.wrap_json('["value2"]')))

But this is ugly and tedious to do so for any inner json I need.

Apparently, the culprit is get_json_value that returns BasicCondition, which doesn't support JSON functions. But get_json_value returns a json that can be used for all other JSON operators, so it should return a different type that allows that.

I managed to make such type by naively creating a new class that derives from both BasicCriterion and JSON:

class JsonCriterion(BasicCriterion, JSON):
    pass

Then I used it instead of get_json_value, and it managed to create the correct sql query I needed:

    .where(JsonCriterion(JSONOperators.GET_JSON_VALUE, table.data, JSON.wrap_constant('array_key')).contains('["value2"]'))

If this was the implementation of get_json_value it would work and support any level of inner json objects, not just arrays.

[abrahamko]

I also have a neat implementation that can help with inner calls to get_json_value:

def get_json_value(term: Term, *keys: Union[str, int]) -> JsonCriterion:
    result = term
    for key in keys:
        result = JsonCriterion(JSONOperators.GET_JSON_VALUE, result, JSON.wrap_constant(key))
    return result

This way, instead of calling table.data.get_json_value('key').get_json_value(1).get_json_value('inner_key') one can simply call table.data.get_json_value('key', 1, 'inner_key')

[abrahamko]

This will allow referring to JSON inner objects as issue #268 requested.