JSON_VALUE also for MySQL?

[Ocramius]

I noticed that JSON_VALUE() is available for MariaDB - according to the MySQL docs, it is already available in MySQL ( https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value ) starting from 8.0.21.

Is it worth replicating the code in both namespaces, perhaps?

[Ocramius]

Hmm, never mind, the MySQL version is JSON_VALUE(a, b RETURNING type), which requires a bit trickier parsing and processing 🤔

[Ocramius]

Here's an example function that I've written for now. It's not very flexible, but covers my current use-case:

<?php

declare(strict_types=1);

namespace Core\Infrastructure\Doctrine\Query\Function;

use Doctrine\ORM\Query;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * Custom DQL function that generates a `JSON_VALUE(<StringPrimary>, <StringPrimary> AS CHAR(50))` expression from the
 * DQL function this is mapped to.
 *
 * For example, if this is mapped as `JSON_VALUE_AS_CHAR_255`, `JSON_VALUE_AS_CHAR_255(foo, '$.something') will generate
 * A `JSON_VALUE(t0_.foo, '$.something' RETURNING CHAR(255))` SQL string fragment.
 *
 * EBNF: "JSON_VALUE_AS_CHAR_255" "(" StringPrimary "," StringPrimary ")"
 *
 * @TODO LCP-2612 test me
 */
final class JsonValueAsChar255Function extends Query\AST\Functions\FunctionNode
{
    private Query\AST\Node $field;
    private Query\AST\Node $extract;

    /** {@inheritDoc} */
    public function __construct(string $name)
    {
        parent::__construct($name);

        $this->field = new class extends Query\AST\Node {
            // Dummy stringPrimary that will just fail, in case SQL generation is attempted before parsing
        };
        $this->extract = new class extends Query\AST\Node {
            // Dummy stringPrimary that will just fail, in case SQL generation is attempted before parsing
        };
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        return 'JSON_VALUE('
            . $this->field->dispatch($sqlWalker) . ','
            . $this->extract->dispatch($sqlWalker) . ' RETURNING CHAR(255))';
    }

    public function parse(Parser $parser): void
    {
        $parser->match(Query\Lexer::T_IDENTIFIER);
        $parser->match(Query\Lexer::T_OPEN_PARENTHESIS);

        $this->field = $parser->StringPrimary();

        $parser->match(Query\Lexer::T_COMMA);

        $this->extract = $parser->StringPrimary();

        $parser->match(Query\Lexer::T_CLOSE_PARENTHESIS);
    }
}