How to create a where query for Json in mysql with key having a white space in between the name

Question

With json as

{ "First Name": {"nickname" : "Abc"} }

in json column 'name' of mysql database.

How do I extract such rows in mysql.

Select * from users where name."$.First Name.nickname" = "abc";

won't work. Any solution because I cannot change the json structure in database.

Answer 1

Found the answer:

Select * from users where name."$.""First Name"".nickname" = "abc";

you need to double quote the key of JSON with a space.

Answer 2

First you need to convert json into array using json_decode

$str='{ "First Name": {"nickname" : "Abc"} }';
$data=json_decode($str,TRUE);

The output of json_decode is

Array
(
    [First Name] => Array
        (
            [nickname] => Abc
        )

)

Extract nickname form array

 echo $name=$data['First Name']['nickname'];

And use in query as

Select * from users where name = '$name';