Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Dreamer's avatar

Full text search on json columns

Hello

Been googling a bit now but cant seem to fix the issue. The trouble is that i am using spaties translatable package to translate post contents so all of my title and content and other post data fields are json fields. Now i started to make search functionality for it and the requirement was that it will make fulltext searches on data. But, i cant seem to create fulltext indexes on json columns.


/**
     * Fulltext indexes for tables.
     *
     * @var array
     */

protected $tables = [
        'posts' => [
            'name', 
            'title', 
            'fields_data', 
            'synonyms'
        ],
    ];

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        foreach ($this->tables as $table => $fields) {
            DB::statement("ALTER TABLE ".$table." ADD FULLTEXT fulltext_index (".implode(",", $fields).")");
        }
    }

Error is:

SQLSTATE[42000]: Syntax error or access violation: 3152 JSON column 'name' supports indexing only via generated columns on a specified JSON path. (SQL: ALTER TABLE posts ADD FULLTEXT fulltext_index (name))

0 likes
2 replies

Please or to participate in this conversation.