Home Searching for value within an array in MySQL JSON fields (Laravel/Lumen)
Reply: 1

Searching for value within an array in MySQL JSON fields (Laravel/Lumen)

Mathias
1#
Mathias Published in 2018-02-14 19:01:13Z

This question already has an answer here:

  • Making a Laravel 5.4 query on a JSON field containing a JSON array 1 answer

I am having troubles searching for a value in an array (of paths to images) within a MySQL JSON field. I want to check if a path to an image is stored in the database to prevent deleting the file.

Controller functions I have tried:

public function checkImage($filename){
    $content = Content::whereRaw('JSON_CONTAINS(images->"$.path", lAeH88gIv4HeycQXHeMbT8yOyydybbOVhsSq9eAx.jpeg')->exists();
    $content = Content::whereRaw('json_contains(images->path, \'["lAeH88gIv4HeycQXHeMbT8yOyydybbOVhsSq9eAx.jpeg"]\')')->exists();
    $content = Content::whereRaw('json_contains(images->path, \'["' . $filename . '"]\')')->exists();
    $content = Content::whereRaw('json_contains(images, \'["' . $filename . '"]\')')->exists();
    return response()->json($content);
}

Example JSON:

{
    "id": 4,
    "images": [
        {
            "path": "abc/abc123.jpg",
            "caption": "text",
            "credits": "text"
        },
        {
            "path": "abc/xyz123.jpg",
            "caption": "text",
            "credits": "text"
        },
        {
            "path": "abc/827364527.jpg",
            "caption": "text",
            "credits": "text"
        }
    ],
    "created_at": "2017-12-20 11:40:16",
    "updated_at": "2018-01-04 12:46:37"
}
Mathias
2#
Mathias Reply to 2018-02-15 11:36:48Z

I have found a solution. Below my Controller function:

public function checkIfImageExists(){
$content = Content::where('images', 'like',  '%image-name-123.jpg%')->exists();
return response()->json($content);
}

The functions returns true if the the image is used in the database JSON field. False if it isn't used.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.297516 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO