Searchable column json

[Ezequielcc1]

How I can search by json column ?

I did

protected $searchable = [
    'columns' => [
        'product->"$.name"' => 10,
    ],
];

Now in laravel

[Ezequielcc1]

SOLVED .... I solved it with :
NEED REMOVE " ` " in code below from vendor SearchableTrait

---

$column = str_replace('.', '.', $column); $field = "LOWER(" . $column . ") " . $compare . " ?";

---

[Ezequielcc1]

[justkidding96]

@nicolaslopezj Can you provide a fix for this?

[kmuharam]

For Postgresql overwrite getCaseCompare($column, $compare, $relevance) in model, cast json to text first and then perform search:

        if ($this->isPostgresqlDatabase()) {
            $field = "LOWER(" . $column . "::text) " . $compare . " ?";
            return '(case when ' . $field . ' then ' . $relevance . ' else 0 end)';
        }

[ianfortier]

Solution For SQLite

(this is the worse code of my life, I'm sorry, but that worked ¯_(ツ)_/¯ )

            'products.name->"$.en"' => 100,
            'products.name->"$.fr"' => 100,

    /**
     * Check if used database is SQLITE.
     *
     * @return bool
     */
    private function isSqliteDatabase()
    {
        return $this->getDatabaseDriver() == 'sqlite';
    }

    protected function getCaseCompare($column, $compare, $relevance)
    {
        if ($this->isPostgresqlDatabase()) {
            $field = "LOWER(" . $column . ") " . $compare . " ?";
            return '(case when ' . $field . ' then ' . $relevance . ' else 0 end)';
        }

        if ($this->isSqliteDatabase()) {
            if (strpos($column, '->"$.') !== false) {
                $jsonParts = explode('->"$.', $column);
                $lastJsonPart = str_replace('"', '', end($jsonParts));
                $field = "LOWER(json_extract(" . $jsonParts[0] . ", '$." . $lastJsonPart . "')) " . $compare . " ?";
                return '(case when ' . $field . ' then ' . $relevance . ' else 0 end)';
            }
        }

        $field = "LOWER(" . $column . ") " . $compare . " ?";
        return '(case when ' . $field . ' then ' . $relevance . ' else 0 end)';
    }

[messi89]

This code worked for me on mariadb...

'table.json_column->"$.attr"' => 10

if (strpos($column, '->"$.') !== false) {
     $columnArray = explode('->"$.', $column);
     $column = $columnArray[0];
     $attribute = str_replace('"', '', end($columnArray));
     $field = "LOWER(JSON_EXTRACT(" . $column . ", '$." . $attribute . "')) " . $compare . " ?";
     return '(case when ' . $field . ' then ' . $relevance . ' else 0 end)';
}

[nikhilbaby2000]

Appreciate if a fix is provided as JSON columns are kind of common already/these days. @nicolaslopezj

For time being, I have extracted out your Searchable Trait (into a new trait) and made below changes.

In Modal's $searchable property, I will be defining column like: table_name.json_column->json_key_1->json_key_2 Ex from my scenario: meta->description (table_name is not used as I was searching only in current table.)

I have used -> instead of . because . is already used to separate table name & column name.

To support this notation, I have added edited in getColumns() & getCaseCompare(...) functions.

In getColumns() of Nicolaslopezj\Searchable\SearchableTrait

...
foreach($this->searchable['columns'] as $column => $priority){

    // Modified Vendor: Preparing JSON extract for columns with ->
    if (substr_exist($column, '->')) {
        $columnParts = explode('->', $column);
        $tableColumn = array_shift($columnParts);

        $column = "JSON_EXTRACT({$tableColumn}, '$.". implode('.', $columnParts) ."')";
    }

    $columns[$prefix . $column] = $priority;
}
...

In getCaseCompare() of Nicolaslopezj\Searchable\SearchableTrait

...
$field = "LOWER(`" . $column . "`) " . $compare . " ?";

// Modified Vendor: Removing ` on JSON Extract
if (substr_exist($field, 'JSON_EXTRACT')) {
    $field = str_replace(['`',], ['', ], $field);
}

return ...;
...

PS: substr_exist() is a helper function in my code that checks for occurance of a needle string in another haystack string and returns only boolean true or false -- almost like strpos(). So please make changes at your end with alternative fn or with simply strpos($haystack, $needle) !== false .