Search code examples
mysqlsqljsonmysql-8.0json-extract

How to input dynamic key to json_extract mysql?

I have one table (call it abc) like below: enter image description here

The column named 'likelihood'is a JSON Object (key-value). I have another table (lets call it xyz) that contains CustomerId and key(an integer value) columns. I am joining the two tables above based on customerid like: Select xyz.customerid, key, label from xyz join abc on xyz.customerid = abc.customerid

Question: I want to find label against the key provided in the key column of table xyz, from the JSON Object of the column likelihood in the table abc. All the examples of function JSON_EXTRACT that I have found, are passing hard coded key instead of some dynamic value of key taken from the database.

Solution

  • First problem was: my JSON Object contains keys which are integer values & not alphabets or words. So in order to reference value with an integer as key, we need to write it down like: JSON_EXTRACT(json_object, '$."int_key"')
    Here is my solution:
    Select xyz.customerid,key,JSON_EXTRACT(abc.likelihood,concat('$."',xyz.key,'"')) as label from xyz join abc on xyz.customerid = abc.customerid;