CODEWITHSUNDEEP
phpmysqljsondatabase
Arun Ranga
Arun Ranga

Reputation: 67

How to write SELECT query in mysql json Array data

I am trying to get the email from the array json, but it returns null value 

[
    {
        "name": "Arun", 
        "email": "[email protected]"
    }, 
    {
        "name": "Arun kumar",
        "email": "[email protected]"
    }
]

and my Json query is select json->>"$.name" as email from json

But this query is return null value

Upvotes: 1

Views: 255

Answers (2)

Rajesh Kakkad
Rajesh Kakkad

Reputation: 186

I tested the following and worked for me on your json sample

$json='[{"name": "Arun", "email": "[email protected]"}, {"name": "Arun kumar", "email": "[email protected]"}]';
$data=json_decode($json,true);

foreach ($data as $key => $value) {
    echo $value["email"] . "<br>";
}

Upvotes: 2

Rahul
Rahul

Reputation: 18557

This should work,

JSON_EXTRACT(yourstring, '$[*].email')

yourstring - your json data or field in database
$ is json syntax to search json object * means all multidimensional array
email check email in * values

Upvotes: 2

Related Questions