Is there a way to use json_table through query builder?

https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Hi, I am struggling to do this …
Query builder will automatically adding parentheses () and this will break the query …

how can I implement this function?

You can use raw sql expressions

or if you want to not having all in a string, you can use something like this

$query->func()->JSON_TABLE([
    'expr',
    $query->expr()
        ->add($query->expr('path'))
        ->add($query->expr('COLUMNS'))
        ->add($query->expr('(columns)'))
        ->setConjunction(' '),
]);
// this code might need some touches

still not working

here is my code

$json_table = $query->func()->JSON_TABLE([
‘cj.v’,
$query->expr()
->add($query->expr(“‘$[*]’”))
->add($query->expr(‘COLUMNS’))
->add($query->expr(‘(row_number FOR ORDINALITY , name varchar(255) PATH '$.name')’))
->setConjunction(’ '),
]);

$query->select([‘t.id’]);
$query->from([‘t’ => ‘table’, ‘jt’ => $json_table]);

this is output
[sql] => SELECT t.id FROM table t, (JSON_TABLE(:param0, (path COLUMNS (columns)))) jt

it should be like this
[sql] => SELECT t.id FROM table t, JSON_TABLE(:param0, path COLUMNS (columns)) jt

Yeah it adds the parenthesis, then i suggest go to the raw expresions route.

$json_table = $query->expr("JSON_TABLE(col, path COLUMNS (columns))");
$query->select(['t.id']);
$query->from(['t' => 'table', 'jt' => $json_table]);

Better but not perfect still there is extra parenthesis
[sql] => SELECT t.id FROM table t, (JSON_TABLE(col, path COLUMNS (columns))) jt

it should be like this
[sql] => SELECT t .id FROM table t , JSON_TABLE(col, path COLUMNS (columns)) jt

Hint:
I believe function ->from is responsible for this adding parenthesis …

That last set of parenthesis, does it break the query? I believe it should execute.

Instead of expr you can try identifier if you got the quote disabled, it should print directly, its a dirty hack :stuck_out_tongue:

1- Yes Raul338, it breaks the code.
2- Give me example for using identifier.

From query::identifier

Do not use this method to inject SQL methods or logical statements.

$json_table = $query->identifier("JSON_TABLE(col, path COLUMNS (columns))");
$query->select(['t.id']);
$query->from(['t' => 'table', 'jt' => $json_table]);

It will only work if you have the autoQuoting disabled (which is the default)

still same friend …
[sql] => SELECT t.id FROM table t, (JSON_TABLE(col, path COLUMNS (columns))) jt

My problem with parenthesis.

Then I’m out of ideas :sob:

I think you could open an issue to suggest not using parenthesis for that function on the from clause

Thanks a lot Raul for your help and try,

where I need to open this issue?

In the framework repo