MySQL supports the native JSON data type since version 5.7.8. The native JSON data type allows you to store JSON documents more efficiently than the JSON text format in the previous versions.

MySQL stores JSON documents in an internal format that allows quick read access to document elements. The JSON binary format is structured in the way that permits the server to search for values within the JSON document directly by key or array index, which is very fast.

The storage of a JSON document is approximately the same as the storage of LONGBLOB or LONGTEXT data.

Json Data type

CREATE TABLE events (
    ...
    browser_info JSON,
    ... 
);

Insert into json column

INSERT INTO events(browser_info) 
VALUES (
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
)

Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.

Json Object

Evaluates a (possibly empty) list of key-value pairs and returns a JSON object containing those pairs. An error occurs if any key name is NULL or the number of arguments is odd.

SELECT JSON_OBJECT('id',u.id, 
'firstName',u.first_name, 
'lastName',u.first_name) as jsonUser
FROM user as u;

Json Array

Evaluates a (possibly empty) list of values and returns a JSON array containing those values.

SELECT JSON_ARRAY(u.id, 
u.first_name, 
u.first_name) as jsonUser
FROM user as u;

Json Object Agg
Return result set as a single JSON object
Takes two column names or expressions as arguments, the first of these being used as a key and the second as a value, and returns a JSON object containing key-value pairs. Returns NULL if the result contains no rows, or in the event of an error. An error occurs if any key name is NULL or the number of arguments is not equal to 2.

SELECT JSON_OBJECTAGG(u.id, u.firstName, u.lastName) as jsonData
FROM user as u;

// output
{
  "id": 1,
  "firstName": "John",
  "lastName": "Peter"
}

Json Array Agg

Return result set as a single JSON array
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined. The function acts on a column or an expression that evaluates to a single value. Returns NULL if the result contains no rows, or in the event of an error.

SELECT JSON_PRETTY(JSON_OBJECT('userId', u.id, 'cards', cardList)) as jsonData
FROM user as u
LEFT JOIN (SELECT c.user_id, 
    JSON_ARRAYAGG(
        JSON_OBJECT(
        'cardId', c.id,
        'cardNumber', c.card_number)
        ) as cardList
    FROM card as c
    GROUP BY c.user_id) as cards ON u.id = cards.user_id;

{
  "cards": [
    {
      "cardId": 4,
      "cardNumber": "2440531"
    },
    {
      "cardId": 11,
      "cardNumber": "4061190"
    }
  ],
  "userId": 1
}

How to accomplish JSON_ARRAYAGG before version 5.7.8

SELECT JSON_PRETTY(JSON_OBJECT('userId', u.id, 'cards', cardList)) as jsonData
FROM user as u
LEFT JOIN (SELECT c.user_id, 
    CONCAT('[', GROUP_CONCAT(
        JSON_OBJECT(
        'cardId', c.id,
        'cardNumber', c.card_number)
        ), ']') as cardList
    FROM card as c
    GROUP BY c.user_id) as cards ON u.id = cards.user_id;

Json Pretty

Provides pretty-printing of JSON values similar to that implemented in PHP and by other languages and database systems. The value supplied must be a JSON value or a valid string representation of a JSON value. 

SELECT JSON_PRETTY(JSON_OBJECT('id',u.id, 
'firstName',u.first_name, 
'lastName',u.first_name)) as jsonUser
FROM user as u;

Json Extract

 json_extract(json_doc, path[, path] ...) 

Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.