Doctrine search in json_array

Question

I'm trying to make a basic search inside a json type in Doctrine+Symfony 3. My field is declared like this:

 /**
 * @ORM\Column(name="data", type="json_array", nullable=true)
 */
 private $data;

This was the code in my repository working pretty well on MySQL:

public function searchByKeyword($kw)
{
    $kw = str_replace(" ","%",$kw);
    return $this->createQueryBuilder("d")
        ->join("d.product", "p")
        ->where("d.data like :search")
        ->setFirstResult(0)
        ->setMaxResults(50)
        ->orderBy("p.createdOn", "DESC")
        ->setParameter("search", "%" . $kw . "%")->getQuery()->execute();
}

I moved on Postgresql 9.5 that can handle JSON type and doesn't use TEXT for json arrays. I have this error:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown

And I'm pretty sure I should cast the json field to text to resolve my issue, but I tried (d.data)::TEXT and d.data::TEXT and it doesn't work.

Answer 1

There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. You will love jsonb in Postgres 9.4, where this is possible. More details in this related answer on dba.SE (last chapter)

Seen here: https://stackoverflow.com/a/24296054/652318

Here is something else relevant: https://stackoverflow.com/a/32843380/652318

You might have to create a new type that transcodes this to use the :: notation.