How to fetch specific values from json in MySQL

Question

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

Barmar · Accepted Answer · 2020-05-08 08:09:35Z

1

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

edited May 8, 2020 at 8:09

answered May 8, 2020 at 7:58

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

4 Comments

Sophia Wilson Over a year ago

Now I'm getting mean in "r-2" format but resp and status is still not coming.

Barmar Over a year ago

My demo uses MySQL 5.7.

Sophia Wilson Over a year ago

Thanks a lot, it worked. Is there a way to remove the double quote?

Barmar Over a year ago

Use JSON_UNQUOTE().

Collectives™ on Stack Overflow

How to fetch specific values from json in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related