JSON in MySQL

JSON And MySQL

JavaScript Object Notation(JSON) is now most popular standard for exchanging data.

JSON is language independent and supports by all programming scripts.

JSON is lighted weighted string and having syntax like array to store values in keys.

JSON is also alternative for replacing traditional data exchanging standard XML.

Since JSON became standard for exchanging data between APIs, Most of the API in php, java, nodejs, phython, ruby, javascript, ASP etc are encouraging data exchanging with JSON.

So latest MySQL 5.7 is launched with JSON data type, Now JSON is available as a datatype in MySQL.

Just like int, varchar, text, longtext Now JSON is also a datatype for storing data into MySQL database.

Basically JSON is a string so most of the programmers started storing large arrays into database by converting it into JSON in text or longtext datatype.

and cause of it's popularity MySQL 5.7 providing a standard JSON datatype to encouraging programmers to keep use JSON for storing data into JSON strings.

Also MySQL 5.7 providing direct operations on JSON so now you can create SQL Queries for merging JSON or counting keys of JSON directly with SQL queries.

Below is detailed Description of MySQL 5.7 latest functions:

JSON_ARRAY()         

This will return JSON array

SELECT  JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())

OUTPUT : [1, "abc", null, true, "11:30:24.000000"]

JSON_APPEND() / JSON_ARRAY_APPEND()

JSON_APPEND() function is replaced by JSON_ARRAY_APPEND() in latest version.

For appending data at the end of JSON string.

Example:

SET @json = '{"x": 1}';

SELECT JSON_ARRAY_APPEND(@json, '$',  'z');

OUTPUT : [{"x": 1}, "z"] 

JSON_ARRAY_INSERT() 

For inserting element into JSON array

SET @json = '["x", {"z": [21]}, [80, 81]]';

SELECT JSON_ARRAY_INSERT(@json, '$[1]', 'y');

OUTPUT : ["x", "y", {"z": [1, 2]}, [3, 4]]

JSON_EXTRACT()

For extracting element from JSON array

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');

OUTPUT : 20

JSON_CONTAINS() 

This will return 1 

if value match to specified key 

This will return 0

if value not match to specified key

SET @json = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS(@json,'1', '$.a');

OUTPUT : 1

JSON_CONTAINS_PATH() 

This will check one or all JSON path

if match specified path this will return 1.

if not match specified path this will return 0.

SET @json = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS_PATH(@json, 'one', '$.a');

OUTPUT : 1

JSON_DEPTH()

This will return maximum depth of JSON 

SELECT JSON_DEPTH('[10, {"a": 20}]');

OUTPUT : 3

JSON_KEYS() 

This will return Array of keys from JSON

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');

OUTPUT : ["a", "b"]                            |

JSON_LENGTH() 

This will return Number of elements from JSON 

SELECT JSON_LENGTH('[1, 2, {"a": 3}]');

OUTPUT : 3

JSON_MERGE() 

This will merge JSON strings

SET  @json1='[1, 2]',

SET  @json2='["a", "b"]',

SET  @json3='[true, false]'

SELECT JSON_MERGE(json1,json2,json3 );

OUTPUT : [1, 2, "a", "b", true, false]

JSON_OBJECT()

This will create JSON object

SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

OUTPUT : {"id": 87, "name": "carrot"}

JSON_QUOTE() 

This will quote JSON string

SELECT JSON_QUOTE('[1, 2, 3]');

OUTPUT : "[1, 2, 3]"

JSON_REMOVE() 

This will be remove data from JSON string

SET @json = '["a", ["b", "c"], "d"]';

SELECT JSON_REMOVE(@json, '$[1]');

OUTPUT : ["a", "d"]

JSON_REPLACE() 

This will replace values in JSON string

SET @json = '{ "a": 1, "b": [2, 3]}';

SELECT JSON_REPLACE(@json, '$.a', 10);

OUTPUT : {"a": 10, "b": [2, 3]}                              |

JSON_SEARCH() 

This will retrun path to value within JSON

SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}]';

SELECT JSON_SEARCH(@json, 'all', 'abc');

OUTPUT : ["$[0]", "$[2].x"]

JSON_SET() 

This will Insert data into JSON

SET @j = '{ "a": 1, "b": [2, 3]}';

SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');

OUTPUT : {"a": 10, "b": [2, 3], "c": "[true, false]"}

JSON_TYPE() 

This will return type of JSON value

SET @j = '{"a": [10, true]}';

SELECT JSON_TYPE(@j);

OUTPUT : OBJECT        

JSON_UNQUOTE() 

This will unquote JSON value

SET @j = '"abc"';

SELECT JSON_UNQUOTE(@j);

OUTPUT : abc

JSON_VALID() 

This will check whether JSON value is valid or not

if valid then return 1

SELECT JSON_VALID('{"a": 1}');

OUTPUT : 1

These are new functions which is now supported in MySQL for 

performing operation on JSON.

Thanks for reading if you have any question add in comments section.