How to write SELECT query in mysql json Array data

Question

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

what kind of syntax it is select json->>"$.name" as email from json? — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jun 11, 2019 at 6:18
you want to fetch name or email in other words Arun or [email protected]? — Rahul
– Rahul, Commented Jun 11, 2019 at 6:50
@ArunRanga, In order to use JSON type in MySQL you must have your MySQL version >= 8.x. Search something in this JSON is very complicated and sometimes not possible. — Alok Mali
– Alok Mali, Commented Jun 11, 2019 at 6:51

Lakhwinder Singh · Accepted Answer · 2019-06-11 08:11:44Z

2

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

edited Jun 11, 2019 at 8:11

Lakhwinder Singh

5,5825 gold badges30 silver badges52 bronze badges

answered Jun 11, 2019 at 6:53

Rajesh Kakkad

1867 bronze badges

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

Comments

Rahul · Accepted Answer · 2019-06-11 06:46:18Z

2

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

answered Jun 11, 2019 at 6:46

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

How to write SELECT query in mysql json Array data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related