Addition of two integers

5+5; // 10

Multiplication of two integers

5*5; // 25

If we add a number to a string, the number will be converted into a string and the result will be a concatenation of the two strings

5+'12'; // 512

String multiplied by an integer

5*'5'; // 25

Multiplication of a string (but not numeric string) and a number

5*'t'; // NaN

The first operation of the expression is addition and then concatenation

5+5+'2'; // 102

Function

JSON_PRETTY

prettifies JSON content

SELECT 
    JSON_PRETTY(document_details) 
FROM students

Note:

MariaDB

does not include

JSON_PRETTY

function

Function

JSON_TYPE

takes JSON content and returns its type

SELECT 
    JSON_TYPE('{}'),  -- returns OBJECT
    JSON_TYPE('[]'),  -- returns ARRAY
    JSON_TYPE(NULL)   -- returns NULL

Function

JSON_ARRAY

takes parameters as array members and returns JSON string as array

SELECT
    JSON_ARRAY("2020-05-25", "2030-05-18", "number", 5)

Function

JSON_OBJECT

returns JSON string. It takes comma separated key / value pairs as parameters

INSERT INTO students (
    first_name, 
    last_name, 
    gender, 
    points, 
    document_details
) 
VALUES (
    'Beka', 
    'Gelashvili', 
    '1', 
    '82', 
    JSON_OBJECT('issue_date', "2020-05-25", "expire_date", "2030-05-18", "number", 5)
)

Filters with JSON keys. In this example the result will be filtered with

number

and

issue_date

keys of

document_details

column

SELECT *
FROM students
WHERE 1 
    AND JSON_EXTRACT(document_details, '$.number') = 8
    AND JSON_EXTRACT(document_details, '$.issue_date') = '2020-05-18'
ORDER BY id DESC

Extracts values from JSON object. In this example we extract two values:

number

,

issue_date

SELECT
    document_details,
    JSON_EXTRACT(document_details, '$.number'),
    JSON_EXTRACT(document_details, '$.issue_date'),
    JSON_UNQUOTE(JSON_EXTRACT(document_details, '$.issue_date'))
FROM students

Note: We can use

JSON_UNQUOTE()

function to remove quotes around JSON string values

Adds new row with JSON column

document_details

INSERT INTO students (
    first_name, 
    last_name, 
    gender, 
    points, 
    document_details
) 
VALUES (
    'Beka', 
    'Gelashvili', 
    '1', 
    '82', 
    '{"issue_date":"2020-05-18", "expire_date":"2030-05-18", "number":"ET2312UO"}'
);

Returns database version

SELECT 
    VERSION()

Equivalent of the query above is to use system variable

@@VERSION

SELECT 
    @@VERSION

Adds

JSON

type column for storing JSON objects / arrays

ALTER TABLE students
ADD  COLUMN document_details JSON