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

Question

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"
}

What is going wrong? What did you expect to happen instead? Is there an error message? Please provide a minimal reproducible example. — jpaugh
– jpaugh, Commented Feb 14, 2018 at 19:05

Mathias · Accepted Answer · 2018-02-15 11:36:48Z

0

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.

answered Feb 15, 2018 at 11:36

Mathias

616 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related