Laravel fetching JSON column MariaDB syntax error

Question

Im trying to fetch some datas depending on the JSON column meta. However, something weirds happen around the -> symbol.

File::whereJsonContains('meta->serie', 'value')->toSql();

output

"select * from `files` where json_contains(`meta`->'$.\"serie\"', ?)"

Here is the error I get

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."serie"', ?)' at line 1 (SQL: select * from files where json_contains(meta->'$."serie"', "check_up"))

Schema

class File extends Model {

    protected $fillable = ['filename', 'mime-type', 'path', 'meta', 'type'];

    protected $casts = [
        'meta' => 'array'
    ];

    const PATH = 'files';

    public function uploadable() {
        return $this->morphTo();
    }

    public function receivable() {
        return $this->morphTo();
    }
}

Controller

class FilesController extends Controller {

    public function download(Request $request) {
        $data = $this->validate($request, [
            'type' => 'required|alpha_dash',
            'meta' => 'sometimes|required',
        ]);

        $search = [
            ['type', $data['type']],
        ];

        if ($request->has('meta')) {
            foreach (json_decode($data['meta']) as $key => $value) {
                $search[] = ["meta->$key", 'like', $value];
            }
        }

        $files = File::where($search)->get();

        return response()->json($files);
    }
}

I tried using a regular where but it throws the same error. Any idea?

Can you post your table schema and where statement you trying to implement — Seva Kalashnikov, Jul 30 '18 at 21:53
I think it's related to this [issue](https://github.com/laravel/framework/pull/24542) MariaDB doesn't handle JSON queries the same way Mysql does — David Nathanael, Jul 30 '18 at 22:11

score 1 · Accepted Answer · edited Jul 30 '18 at 23:29

1

Try this:

$data = $request->all();

$query = File::where('type', $data['type']);

if ($request->has('meta')) {
    foreach (json_decode($data['meta']) as $key => $value) {
        $query->whereRaw("JSON_CONTAINS(meta, '\"{$value}\"', '$.{$key}')");    
    }
}

$files = $query->get();

edited Jul 30 '18 at 23:29

David Nathanael

95
10

answered Jul 30 '18 at 22:14

Seva Kalashnikov

4,262
2
21
36

i think that's the way to go but for some reasons, the rows im querying are not found. However when I remove the `json_contains` part, I can retreive them easily – David Nathanael Jul 30 '18 at 22:51
Its the details I guess.. you need to troubleshoot the data you receive and compare it agains json stored.. well at least I got you heading in the right direction right? – Seva Kalashnikov Jul 30 '18 at 22:56
yes. here's how the data is stored in the meta column `"{\"angle\":\"lower\",\"serie\":\"check_up\"}"` how would you query that? – David Nathanael Jul 30 '18 at 22:58
1

Maybe try `$query->whereRaw("JSON_CONTAINS(REPLACE(meta, '\\', ''), '{$value}', '{$key}')";` but its such a workaround for your case, might work but not sure if its a good approach – Seva Kalashnikov Jul 30 '18 at 23:04
1

Finally, you got me in the right direction. I wasnt inserting the data correctly. I use json_encode when I didnt need to do it. Thanks – David Nathanael Jul 30 '18 at 23:13
btw you are missing a closing parenthesis at the end of `$query->` line – David Nathanael Jul 30 '18 at 23:15
Yes I noticed that some time ago and made an edit but you must've still used unedited code xD – Seva Kalashnikov Jul 30 '18 at 23:16

Laravel fetching JSON column MariaDB syntax error

1 Answers1