Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get values from MySQL(5.6) column if that contains json document as string

Tags:

json

mysql

How to get values from MySQL(5.6) column if that contains JSON document as a string

For example, if we have a table - employee in that we have three columns id, name and educations. and column educations contains data as a JSON document

{"ug":"bsc","pg":"mca","ssc":"10th"} 

I need the value of ug and pg from educations column

Can we do that using MySQL(5.6) queries?

like image 895
Poppy Avatar asked Jun 14 '16 15:06

Poppy


People also ask

Does MySQL 5.6 support JSON data type?

- Supports all the JSON types – Numbers , string, Bool , objects & arrays.

How do I query a JSON column in MySQL?

How to Retrieve data from JSON column in MySQL. MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are.

Does MySQL 5.7 support JSON data type?

As of MySQL 5.7. 8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents.


2 Answers

To be able to do what you want to, you need MySQL 5.7.8+. Since 5.7.8 you can use JSON_EXTRACT function to extract a value from a JSON string:

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');  +---------------------------------------------------------+ | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') | +---------------------------------------------------------+ | "Aztalan"                                               | +---------------------------------------------------------+ 

Taken from here.

In MySQL 5.6 you just can't get the value you want as MySQL doesn't know anything about what a JSON object is. So your options are:

  • Upgrade to 5.7.8+
  • Parse the query result with something that handles JSON:
    • Could be PHP json_decode (or equivalent in your language)
    • An online tool like http://json.parser.online.fr/
like image 169
Alvaro Flaño Larrondo Avatar answered Oct 08 '22 22:10

Alvaro Flaño Larrondo


In MySQL 5.6, by default JSON_EXTRACT is not available by default.
If you still need to access json data in MySQL 5.6, you need to write custom function.

DELIMITER $$  DROP FUNCTION IF EXISTS `json_extract_c`$$  CREATE DEFINER=`root`@`%` FUNCTION `json_extract_c`(   details TEXT,   required_field VARCHAR (255) ) RETURNS TEXT CHARSET latin1 BEGIN   RETURN TRIM(     BOTH '"' FROM SUBSTRING_INDEX(       SUBSTRING_INDEX(         SUBSTRING_INDEX(           details,           CONCAT(             '"',             SUBSTRING_INDEX(required_field,'$.', - 1),             '"'           ),           - 1         ),         '",',         1       ),       ':',       - 1     )   ) ; END$$  DELIMITER ; 

This will help. I have created it and tested.

like image 39
Rahul Avatar answered Oct 08 '22 22:10

Rahul