Some JSON related functions in MySQL

 create table user_info(

id int auto_increment primary key,

info json

)

insert into

user_info(id, info)

values

(1, '{"firstName":"sangram","lastName":"desai"}')

select

id,

JSON_EXTRACT(info, '$.firstName'),

JSON_EXTRACT(info, '$.lastName')

from

user_info;

--check if json string is valid or return 1 on true & 0 on false

select

JSON_VALID(info)

from

user_info;

--Merge JSON documents, replacing values of duplicate keys

SELECT

JSON_MERGE_PATCH('{"name": "sangram"}', '{"name":"sagar","age":89}');

Output:

+----------------------------------------------------------------------+

| JSON_MERGE_PATCH('{"name": "sangram"}', '{"name":"sagar","age":89}') |

+----------------------------------------------------------------------+

| {"age": 89, "name": "sagar"} |

+----------------------------------------------------------------------+

--Merge JSON documents, preserving duplicate keys

SELECT

JSON_MERGE_PRESERVE('{"name": "sangram"}', '{"name":"sagar","age":89}');

Output:

+-------------------------------------------------------------------------+

| JSON_MERGE_PRESERVE('{"name": "sangram"}', '{"name":"sagar","age":89}') |

+-------------------------------------------------------------------------+

| {"age": 89, "name": ["sangram", "sagar"]} |

+-------------------------------------------------------------------------+

--JSON_MERGE

select JSON_MERGE('{"name": "sangram"}', '{"name":"sagar","age":89}');

JSON_MERGE Deprecated but same as JSON_MERGE_PRESERVE

--JSON_REMOVE

select JSON_REMOVE(info,'$.firstName') from user_info;

removes firstName key from info column

Output:

+---------------------------------+

| JSON_REMOVE(info,'$.firstName') |

+---------------------------------+

| {"lastName": "desai"} |

+---------------------------------+

JSON_ARRAY_APPEND same as JSON_APPEND which is Deprecated:

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

SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);

in @j we have array ["b","c"] as 1 st index where our value 1 is appended

Output:

+----------------------------------+

| JSON_ARRAY_APPEND(@j, '$[1]', 1) |

+----------------------------------+

| ["a", ["b", "c", 1], "d"] |

+----------------------------------+

SELECT JSON_ARRAY_APPEND(@j, '$', 1);

Output:

+-------------------------------+

| JSON_ARRAY_APPEND(@j, '$', 1) |

+-------------------------------+

| ["a", ["b", "c"], "d", 1] |

+-------------------------------+

here is main array 1 is appended

-- JSON_PRETTY -format json

SELECT JSON_PRETTY(info) from user_info;

SELECT JSON_QUOTE(cast(info as char)) from user_info;

Output:

+---------------------------------------------------------+

| JSON_QUOTE(cast(info as char)) |

+---------------------------------------------------------+

| "{\"lastName\": \"desai\", \"firstName\": \"sangram\"}" |

+---------------------------------------------------------+

here double quotes got escaped

SELECT JSON_UNQUOTE(JSON_QUOTE(cast(info as char))) from user_info;

Output:

+-----------------------------------------------+

| JSON_UNQUOTE(JSON_QUOTE(cast(info as char))) |

+-----------------------------------------------+

| {"lastName": "desai", "firstName": "sangram"} |

+-----------------------------------------------+

convert escaped json string to normal json

--JSON_OBJECT

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

Output:

+-----------------------------------------+

| JSON_OBJECT('id', 87, 'name', 'carrot') |

+-----------------------------------------+

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

+-----------------------------------------+

create JSON object from key value pair passed as parameter ,number of param should be alway even here else error

JSON_ARRAY:

create aray from passed parameters

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

Output:

+---------------------------------------------+

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

+---------------------------------------------+

| [1, "abc", null, true, "14:01:52.000000"] |

+---------------------------------------------+