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 

Question

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.

<?php

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