Your Location is: Home > Symfony

Doctrine SchemaTool error on adding index by JSONB field (PostgresQL): There is no column with name metadata->>'account' on table

From: Skopje View: 3298 Eduard Sukharev 


I have an entity with metadata stored in a JSONB field. In order to make fast queries, I want to add an index by some of the key-values in metadata. I also want SchemaTool to track and generate migrations for me, so I want to define those Indexes as annotations.


namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

 * @ORM\Table(indexes={
 *     @ORM\Index(columns={"metadata->>'account'"})
 * })
class Statistics
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(name="id", type="bigint")
    private int $id;

     * @ORM\Column(name="metadata", type="json", options={"jsonb"=true})
    private array $metadata;

However, when I run bin/console do:mi:diff I get the following error:

In SchemaException.php line 86:
  There is no column with name 'metadata->>'account'' on table 'statistics'.                                                                              

I have tried different combinations of parenthesis and quotes, but failed to find working one. Any ideas if it is supported or what the proper syntax would be?

Best answer