CODEWITHSUNDEEP
mysql
Sophia Wilson
Sophia Wilson

Reputation: 477

How to fetch specific values from json in MySQL

I have a table, in MySQL Version = 5.7

Table_1 <-

ID        Json
IR-1      {Json}
IR-2      {Json}

Sample Json String

{
  "flag": false,
  "resp": "RTUI",
  "mean": "r-2",
  "details": {
    "product": "IR JAD",
    "status": "failed",
    "datetime": "26/09/2017"
  }
}

I Want to fetch the field resp, mean and status, in below mentioned format. I'm using the below mentioned query but it works only to fetch the status in ["failed"] format.

select ID,
json_extract(Json, '$.*.resp') AS resp,
json_extract(Json, '$.*.mean') AS mean,
json_extract(Json, '$.*.status') AS status
from Table_1
where ID in ('IR-1','IR-2');

Required output:

ID     resp     mean      status
IR-1   RTUI     r-2       failed

Upvotes: 0

Views: 37

Answers (1)

Barmar
Barmar

Reputation: 780798

You shouldn't have .* for top-level properties.

Also, it would probably be better to specify the details property explicitly, than using a wildcard to access the nested status property.

select ID,
    json_extract(Json, '$.resp') AS resp,
    json_extract(Json, '$.mean') AS mean,
    json_extract(Json, '$.details.status') AS status
from Table_1
where ID in ('IR-1','IR-2');

DEMO

Upvotes: 1

Related Questions