The JSON Data Type in Mysql 5.7

 As of Mysql 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON documents. These are the advantages over storing JSON in a string column:

1. Automatic validation of JSON doc
2. Optimized storage format, it is converted to an internal format that permits quick read access to the document elements.

The space required to store a JSON document is roughly the same as for LONGBLOB (L + 4 bytes, where L < 232) > L represents the actual length in bytes of a given string value.

important notes
SELECT JSON_TYPE('hello'); -> ERROR 3146 (22032) # You must specify JSON_TYPE('"hello"');

limitations

• A JSON column cannot have a non-NULL default value.
• This 'first key wins' handling of duplicate keys is not consistent with RFC 7159. This is a known issue in MySQL 5.7, which is fixed in MySQL 8.0. (Bug #86866, Bug #26369555)

replication
It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable.

json functions avaiable

• A JSON column cannot have a non-NULL default value.

collation and charset
MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation. Strings in other character sets are converted to utf8mb4 as necessary.

SELECT CHARSET(@j), COLLATION(@j);

• json_array
  A JSON array contains a list of values separated by commas and enclosed within [ and ] characters: ["abc", 10, null, true, false]

• json_object
  A JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters: {"k1": "value", "k2": 10}

Managing a Json object value

JSON values are written as strings. Validation of the string!
Attempting to insert a value into a JSON column succeeds if the value is a valid JSON value, but fails if it is not

select meta_j from ms ->
{"brand_id": "594", "_STORE_customer_state": "OC", "quota_used_is_accurate": "0", "registration_client_id": "fe:10.110.11.123"}

insert json doc

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:

• insert a key-value
  JSON_INSERT() adds new values but does not replace existing values:

select meta_j a, json_extract(meta_j, '$.result') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: "Good"

update ms set meta_j=json_insert(meta_j, '$.result', 'Bad');

select meta_j a, json_extract(meta_j, '$.result') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: "Good"

• replace a value of a key
  JSON_REPLACE() replaces existing values and ignores new values

-- no changes
select meta_j a, json_replace(meta_j, '$.resultAAAAAAA', 'Bad') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}

merge (update)

select JSON_MERGE('["a", 1]', '{"key": "value"}');
select JSON_MERGE('{"key": "value"}', '{"key": "value"}');

select meta_j a, json_merge(meta_j, '{"basket":"Empty"}') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: {"basket": "Empty", "result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}

loop up (select)

To look up this particular sentence employing mascot as the key, you can use the column-path operator ->
In MySQL 5.7.9 and later, you can use column->path with a JSON column identifier and JSON path expression as a synonym for JSON_EXTRACT(column, path)

mysql> select meta_j->"$.registration_client_id" from ms;
+------------------------------------+
| meta_j->"$.registration_client_id" |
+------------------------------------+
| "fe:10.110.11.123"                 |

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
mysql> select json_extract(meta_j,'$.registration_client_id')from ms;
+-------------------------------------------------+
| json_extract(meta_j,'$.registration_client_id') |
+-------------------------------------------------+
| "fe:10.110.11.123"                              |

set (replace)
replaces values for paths that exist and adds values for paths that do not exist:.

-- add 
select meta_j a, json_set(meta_j, '$.basket', 'Full') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: {"basket": "Full", "result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
1 row in set (0.00 sec)

--replace
select meta_j a, json_set(meta_j, '$.result', 'Full') m from ms\G
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: {"result": "Full", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}

remove

JSON_REMOVE() takes a JSON document and one or more paths that specify values to be removed from the document.

mysql> select meta_j a, json_remove(meta_j,'$.result') m from ms
a: {"result": "Good", "brand_id": "999", "registration_client_id": "fe:10.110.11.123"}
m: {"brand_id": "999", "registration_client_id": "fe:10.110.11.123"}

json column index

https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index
JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column

Other resources
MySQL docs
json-datatype
json-function