How to extract a value from a JSON string by key

Question

I have a column which has Json string records. I want to parse the json and fetch the value of a particular key from within the select statement.

Sample JSON

{"kofaxDocId":"8ae16f46-c68f-11e5-8105-0e15fb39b661","systemDocType":"Loan Application","requestId":"c770a940-b9f3-4c41-aee6-3e08c1470ec6","docType":"Loan Application","confidence":0.6499999761581421,"engineType":"kofax","completionStatus":"Suggested"}

I want my select query to fetch only the value of the key "confidence". I tried using Regex and Substring, but since the json length is not fixed, it doesn't fetch correct values for all the records.

i tried these

SELECT substring(extended_metadata, ('"confidence":', extended_metadata ))  FROM documents ;

SELECT json_extract(extended_metadata,'confidence') CONFIDENCE from documents;

The Json_extract() isn't supported with my MYSQL version.

Appreciate help.

Answer 1

MySQL has got support for JSON in version 5.7.7 http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

You have to resolive it purely in mysql then I am afraid you have to treat it as a string and cut the value out of it (just normal string functions or use regular expressions) This is not elegant but it will work

CREATE TABLE testjson (`jsonfield` varchar(2000)) ;

INSERT INTO testjson (`jsonfield`) VALUES ('{"kofaxDocId":"8ae16f46-c68f-11e5-8105-0e15fb39b661","systemDocType":"Loan Application","requestId":"c770a940-b9f3-4c41-aee6-3e08c1470ec6","docType":"Loan Application","confidence":0.6499999761581421,"engineType":"kofax","completionStatus":"Suggested"}')  ;


SELECT substring(jsonfield, locate('"confidence":',jsonfield)+13, locate(',"', jsonfield, locate('"confidence":',jsonfield))-locate('"confidence":',jsonfield)-13) as confidence_value
  FROM testjson;

This query search for Confidence in your jsondata, then look at the next separator after confidence, and it substract the content between these two index.

Here's a SQL fiddle of the example above: http://sqlfiddle.com/#!9/2edfaf/3/0