Querying a specific JSON key value from mysql

Question

I have a mysql (v5.7.23) table contain a json object like this

[{
    "plant_part": "Leaves, Seed",
    "extract_type": "Ethanol",
    "activity": "Colon Cancer",
    "reference": "6"
  },
  {
    "plant_part": "Leaves, Seed",
    "extract_type": "Ethanol",
    "activity": "Lung Cancer",
    "reference": "6"
  }]

Now i want to get value from only the "activity" key. and this is what I'm trying.

$sql = "select * from table where column like '%".$term."%';

but this will select all value from that column. Is there any way get result from specific key?

I mean something like this?

$sql = "select * from table where column.activity like '%".$term."%';

Thank you.

@Edwin I'm no more qualified to answer that than you, but a limited set of json functions are available in 5.7 - dev.mysql.com/doc/refman/5.7/en/json-function-reference.html — Strawberry
– Strawberry, Commented Aug 20, 2018 at 7:26
@Strawberry before 5.7 I didn't find any reference about it, so I asked because I thought you may know the answer. Thanks anyway. — Edwin
– Edwin, Commented Aug 20, 2018 at 7:30

Nick · Accepted Answer · 2018-08-20 08:25:52Z

2

You could use JSON_EXTRACT to find the values of activity and compare them:

$sql = "SELECT * 
        FROM table
        WHERE JSON_EXTRACT(column,  '$[*].activity') LIKE '%".$term."%'";

For your sample data the JSON_EXTRACT will return ["Colon Cancer", "Lung Cancer"] which can then be compared against e.g. "%Cancer%".

edited Aug 20, 2018 at 8:25

answered Aug 20, 2018 at 8:19

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Querying a specific JSON key value from mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related