Laravel mysql json data selecting

Question

I got a database running on mysql 5.7.11 and has a lot of data into a table with JSON column. I got a raw query like this one.

select * from `note` 
where `note_structure_id` = 3 
and JSON_EXTRACT(LCASE(data), '$._letter') = 'a' 
and JSON_EXTRACT(data, '$._number') = 1

Running in sequel pro or phpmyadmin it gives me a result what i expect;

{"_letter": "A", "_number": 1}

The same query i build with laravel it gives me a result of nothing. An empty array. My code to building the same query i got this kind of code.

$result = \DB::table( 'note' )->where( 'note_structure_id', $structureId );

if( is_array( $query ) ) {
    if( isset( $query['where'] ) ) {
        foreach( $query['where'] AS $field => $value ) {
            if( is_numeric( $value ) ) {
                $result = $result->where( \DB::raw( "JSON_EXTRACT(data, '$._{$field}')" ), '=', $value );
            }
            else {
                $result = $result->where( \DB::raw( "JSON_EXTRACT(LCASE(data), '$._{$field}')" ), '=', strtolower( $value ) );
            }
        }
    }
}

dd($result->get());

Did anyone knows what i did wrong with my code or something. I try everything what's possible for fixing it but without result.

Thanks!

After researching it comes to an conclusion that i need to add JSON_UNQUOTE() around the JSON_EXTRACT() and it works like a charm! — Danny Bevers
– Danny Bevers, Commented Feb 23, 2016 at 22:21

mrhn · Accepted Answer · 2016-02-23 22:31:30Z

1

Saw you fixed it, but why not just do as a raw query, in a more traditional way. Your code is not very readable at the moment.

$results = DB::select('select * from `note` where `note_structure_id` = :id and JSON_EXTRACT(LCASE(data), `$._letter`) = :a and JSON_EXTRACT(data, `$._number`) = :number', ['id' => 3, 'a' => 'a', 'number' => 1]);

answered Feb 23, 2016 at 22:31

mrhn

19.1k6 gold badges31 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Laravel mysql json data selecting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related