JSON_GET_FIELD fails when using integer for array element access

[auroraeosrose]

Following error is produced using JSON_GET_FIELD with an integer value for an array element position

Syntax Error] line 0, col 177: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '0'

JSON_GET_FIELD_AS_TEXT(JSON_GET_FIELD(alias.fieldname, 0), 'value')

json data looks like

[ {"name":"type1", "value":"value"},{"name":"type2", "value":"value"}]

Wanting to query for

fieldname->0->>value = 'something'

If you quote the 0 you can get it to not error, but the SQL produced is incorrect

fieldname->'0'->>value = 'something'

Does not work in the query as the quoted 0 is not seen as an array element in the json - instead postgresql is looking for an object with a property value of "0"

Digging through the AST And parsing it doesn't look like there's a LOT that can really be done to fix this because of they way the StringPrimary node works

The "right" fix is probably to allow either arithmeticprimary or stringprimary for that node but my Doctrine AST foo is weak

For now I simply created my own terrible function and use it in JSON_GET_ARRAY_INDEX('fieldname', 0) and it works just fine but it is annoying :)

class JsonGetArrayIndex extends BaseFunction
{
    protected function customizeFunction(): void
    {
        $this->setFunctionPrototype('(%s -> %s)');
        $this->addNodeMapping('StringPrimary');
        $this->addNodeMapping('ArithmeticPrimary');
    }
}

[martin-georgiev]

#402 attempts to add the missing index support. Can you please confirm it solves your use case?

[auroraeosrose]

I know this is closed but wanted to mention that this works great now that I've had a chance to unwind my hackaround and update stuff in composer :)

Thanks!