Laravel/PDO JSONPATH request on Postgresql

Question

I am trying to make a jsonpath request on a postgresql with laravel/pdo. Here is what I need to archieve.

SELECT i.id as id, i.modele_id as modele_id, i.data as data, m.nom as modele_nom
FROM items i
JOIN modeles m ON (i.modele_id = m.id)
WHERE m.id = 2
AND i.data @? '$[*] ? ( (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i") || exists( @.fields[*] ? (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i")))'

PDO seems to have probleme with ? (question mark) because it is the symbole of parameters. I was trying to set up the request like that:

$query = "SELECT i.id as id, i.modele_id as modele_id, i.data as data, m.nom as modele_nom FROM items i JOIN modeles m ON (i.modele_id = m.id) WHERE m.id = 2 AND i.data ? '?'";

$params = [
'@?',
'$[*] ? ( (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i") || exists( @.fields[*] ? (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i")))'
];

$items = DB::connection('pgsql')->select($query, $params);
dd($items);

But I got an error:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (SQL: SELECT i.id as id, i.modele_id as modele_id, i.data as data, m.nom as modele_nom FROM items i JOIN modeles m ON (i.modele_id = m.id) WHERE m.id = 2 AND i.data @? '$[*] ? ( (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i") || exists( @.fields[*] ? (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i")))')

This is where I don't understand. The built request is exactly what I want and If I echo $query and $params, I got clearly a request with 2 parameters and params array with two parameters too:

dd($query, $params);

"SELECT i.id as id, i.modele_id as modele_id, i.data as data, m.nom as modele_nom FROM items i JOIN modeles m ON (i.modele_id = m.id) WHERE m.id = 2 AND i.data ? '?'"
array:2 [
  0 => "@?"
  1 => "$[*] ? ( (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i") || exists( @.fields[*] ? (@.idParametre == 4 && @.valeur like_regex ".*555.*" flag "i")))"
]

Does somebody know how to handle that?

Best regards and thanks for the reading.

Answer 1

I finally found a way to do it.

$query = "SELECT i.id as id, i.modele_id as modele_id, i.data as data, m.nom as modele_nom 
FROM items i 
JOIN modeles m ON (i.modele_id = m.id) 
WHERE m.id = 2 AND i.data @?? '$[*] ? (
 (
  @.idParametre == 4 && @.valeur like_regex \".*555.*\" flag \"i\")
  || exists( @.fields[*] ?
  (
    @.idParametre == 4 && @.valeur like_regex \".*555.*\" flag \"i\"
  )
))'";

$params = [];

$items = DB::connection('pgsql')->select($query, $params);
dd($items);

You must have php >= 7.4 and escape the @? like that @??

For the rest of the request, it should not use parameters otherwize it doesn't work. Simply escape the " (double quote) in the JSONPATH