JSON Operators

->

Description: Queries data at a position specified in JSON and returns the query result with quotation marks.

Examples:

-- Create a data table.
m_db=# CREATE TABLE muscleape
(
  id       TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category JSON,
  tags     JSON,
  PRIMARY KEY (id)
);
 
-- Insert data.
m_db=# INSERT INTO muscleape (category, tags) VALUES ('{"id": 1,"name": "muscleape"}','[1,2,3]');
m_db=# INSERT INTO muscleape (category, tags) VALUES (JSON_OBJECT("id",2,"name","muscleape_q"),JSON_ARRAY(1,3,5));
 
-- Query the table.
m_db=# SELECT * FROM muscleape;
 id |             category             |   tags    
----+----------------------------------+-----------
 1  | {"id": 1, "name": "muscleape"}   | [1, 2, 3]
 2  | {"id": 2, "name": "muscleape_q"} | [1, 3, 5]
(2 rows)

-- Query data in JSON.
m_db=# SELECT id,category->'$.id',category->'$.name',tags->'$[0]',tags->'$[2]' FROM muscleape;
 id | ?column? |   ?column?    | ?column? | ?column? 
----+----------+---------------+----------+----------
 1  | 1        | "muscleape"   | 1        | 3
 2  | 2        | "muscleape_q" | 1        | 5
(2 rows)



-- Drop the table.
m_db=# DROP TABLE muscleape;

->>

Description: Queries data at a position specified in JSON and returns the query result without quotation marks.

Examples:

-- Create a data table.
m_db=# CREATE TABLE muscleape
(
  id       TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category JSON,
  tags     JSON,
  PRIMARY KEY (id)
);
 
-- Insert data.
m_db=# INSERT INTO muscleape (category, tags) VALUES ('{"id": 1,"name": "muscleape"}','[1,2,3]');
m_db=# INSERT INTO muscleape (category, tags) VALUES (JSON_OBJECT("id",2,"name","muscleape_q"),JSON_ARRAY(1,3,5));
 
-- Query the table.
m_db=# SELECT * FROM muscleape;
 id |             category             |   tags    
----+----------------------------------+-----------
 1  | {"id": 1, "name": "muscleape"}   | [1, 2, 3]
 2  | {"id": 2, "name": "muscleape_q"} | [1, 3, 5]
(2 rows)

-- Query data in JSON.

m_db=# SELECT id,category->>'$.id',category->>'$.name',tags->>'$[0]',tags->>'$[2]' FROM muscleape;
 id | ?column? |  ?column?   | ?column? | ?column? 
----+----------+-------------+----------+----------
 1  | 1        | muscleape   | 1        | 3
 2  | 2        | muscleape_q | 1        | 5
(2 rows)


-- Drop the table.
m_db=# DROP TABLE muscleape;

Comparison Operators

Description: Comparison operators between JSON types support the =, <=>, <>, <, ≤, >, and ≥ operations. The return result is true or false. If the JSON type is compared with other types, the other types are converted to the JSON type before the comparison.

Sorting rule of the JSON type:

• Compare the JSON data types (OPAQUE > TIMESTAMP = DATETIME > TIME > DATE > BOOL > array & > object & > string type > DOUBLE = UINT = INT = DECIMAL > NULL). If the results are the same, compare the contents.
• Compare values between scalars. OPAQUE compares types first. TYPE_STRING > TYPE_VAR_STRING > TYPE_BLOB > TYPE_BIT > TYPE_VARCHAR > TYPE_YEAR. If the types are the same, compare each byte.
• Compare the size of arrays. The size of arrays can be compared by comparing the number of the elements between arrays. The array with more elements is larger. When the number of elements are the same, compare the lengths of elements. If the lengths are the same, compare each key-value pair in sequence. Compare the key first and then the value.

Examples:

mydb=# SELECT CAST(TIME'12:12:00' AS JSON) < CAST(BINARY'100100100' AS JSON);
 ?column? 
----------
 t
(1 row)

mydb=# SELECT CAST('{"jsnid": [true, "abc"], "tag": {"ab": 1, "b": null, "a": 2}}' AS JSON) > CAST('[1, 2, "json", null, [[]], {}]' AS JSON);
 ?column? 
----------
 f
(1 row)

mydb=# SELECT CAST('true' AS JSON) <= CAST('-1.5e2' AS JSON);
 ?column? 
----------
 f
(1 row)

mydb=# SELECT CAST('"abc"' AS JSON) >= CAST('null' AS JSON);
 ?column? 
----------
 t
(1 row)

mydb=# SELECT CAST('0.5e3' AS JSON) = CAST('500' AS JSON);
 ?column? 
----------
 t
(1 row)

Arithmetic Operators

Operators of the JSON type and any type support the –, +, *, and / operations. The DOUBLE type is returned. JSON data of the numeric scalar, Boolean scalar, and string scalar types is converted to the DOUBLE type for computing. JSON data of other types cannot be converted.

Examples:

mydb=# SELECT CAST('true' AS JSON) + CAST('1.5e2' AS JSON);
 ?column? 
----------
      151
(1 row)

mydb=# SELECT CAST('"123"' AS JSON) - 1;
 ?column? 
----------
      122
(1 row)

mydb=# SELECT CAST('5' AS JSON) / 2;
 ?column? 
----------
   2.5000
(1 row)

mydb=# SELECT CAST('3' AS JSON) * 3.33;
 ?column? 
----------
     9.99
(1 row)