Operators for documents [Deprecated]

FB DocStore is deprecated, and users will need to find alternative solutions.

Document item type

A new item type is introduced in MySQL that represents a JSON document, which will be validated during instantiation. With the new document type, we can now introduce new operators that are document specific because we can check that the operands are of document type.

DOCUMENT() built-in function

To create a document item in SQL, you use the DOCUMENT() built-in function, and pass a literal string that represents a JSON object. The result new item will be treated as a document item.

SELECT DOCUMENT ('{"k1":"v1","k2":true,"k3":3}');

-- JSON is validated
SELECT DOCUMENT('{"key"}');
ERROR HY000: Invalid JSON object: '{"key"}', pos 7, error 'Invalid JSON object'.
With the document item type, we introduced the following new operators:

Binary Operator SIMILAR

Usage

-- TRUE
SELECT DOCUMENT('{"k1":"v1", "k2":"v2"}') 
       SIMILAR 
       DOCUMENT('{"k2":"v2", "k1":"v1"}');

-- TRUE
SELECT DOCUMENT('{"k1": [1, "bar", {"k11": "v11", "k12": "v12"}, [{"foo": "bar"}]], "k2": "v2"}')
       SIMILAR
       DOCUMENT('{"k2" : "v2", "k1": [1, "bar", {"k12": "v12", "k11": "v11"}, [{"foo": "bar"}]]}');

-- In where clause
SELECT id
FROM t1
WHERE doc.address NOT SIMILAR DOCUMENT('{"state":"NY", "zipcode":98767}');

Notes

Both operands must both be of the document type which can be a document column, document path, or a literal specified within the DOCUMENT() function. It returns true if the left operand and the right operand contain the exact same key-value pairs, but order doesn't matter. For example, {"k1": "v1", "k2": "v2"} and {"k2": "v2", "k1": "v1"} are similar.

The order of elements in an array however do matter.

Binary Operator SUBDOC

Usage

-- TRUE
SELECT DOCUMENT('{"k2":"v2"}')
       SUBDOC
       DOCUMENT('{"k1":"v1", "k2":"v2"}');

-- TRUE
SELECT DOCUMENT('{"k1":"v1", "k2":"v2"}')
       CONTAINS
       DOCUMENT('{"k2":"v2"}');

Notes

Both operands must both be of the document type which can be a document column, a document path, or a literal specified within the DOCUMENT() function. It returns true if every key-value pair of the left operand is contained in the first level of the right operand. Again, order does not matter for key-value pairs but the level of nesting does matter.

Binary Operator CONTAINS

Usage

-- TRUE
SELECT DOCUMENT('{"k1":"v1", "k2":"v2"}')
       CONTAINS
       DOCUMENT('{"k2":"v2"}');

Notes

This operator is the inverted version of SUBDOC, meaning that x SUBDOC y is true if and only if y CONTAINS x is true.

If you attempt to use CONTAINS with any other type other than the document type, an error will be thrown.

Binary Operator LIKE (overload)

Usage

-- TRUE (document type SIMILAR)
SELECT DOCUMENT('{"k1":"v1", "k2": "v2"}') LIKE DOCUMENT('{"k2": "v2", "k1": "v1"}');

-- TRUE (string matching)
SELECT DOCUMENT('{"k":"v"}') LIKE '{"k%';

-- FALSE (string matching)
SELECT DOCUMENT('{"k":"v"}') LIKE '{"k"    :"v"}';

Notes

This is an existing SQL operator, but its behavior depends on the operand types.

• If both operands are documents (document column, document path, or literal using DOCUMENT() function), then the comparison is equivalent to that of SIMILAR. Every key-value pair must be matched, and the order of keys do not matter.
• If only one operand is a document, the document operand is first casted to a string. After that, the normal LIKE logic will be evaluated. This allows one to compare a document with a string that has wildcards.

Binary Operator = (overload)

Usage

-- TRUE
SELECT DOCUMENT('{"key":[1,2,3]}') = DOCUMENT('{"key":[1,2,3]}');

Notes

This is an existing SQL operator, but its behavior depends on the operand types.

• If both operands are documents (document column, document path, or literal using DOCUMENT() function), the comparison is between the binary format of the two operands. It returns true if they contain exactly the same key-value pairs in the same order.
• If only one operand is a document, it will first cast the document operand to a string, then proceed with the usual scalar equality comparison.

Binary Operator != and <> (overload)

Usage

-- TRUE
SELECT DOCUMENT('{"key":[1,2,3]}') != DOCUMENT('{"key":[1,2]}');
SELECT DOCUMENT('{"key":[1,2,3]}') <> DOCUMENT('{"key":[1,2]}');

-- FALSE
SELECT DOCUMENT('{}') != '{}';
SELECT DOCUMENT('{}') <> '{}';

Note

This is the negation of the = operator.

Regex Wildcards in Document() function

We allow standard MySQL wildcards (%, _, etc) to be used in a JSON string value inside a DOCUMENT() function. Wildcards inside a key are not supported and treated as literal symbols.

Usage

-- TRUE
SELECT DOCUMENT('{"k":"v1"}') LIKE DOCUMENT('{"k":"v_"}');

-- TRUE
SELECT DOCUMENT('{"k":"v1", "k2":"v2"}') CONTAINS DOCUMENT('{"k":"%"}');

-- TRUE
SELECT DOCUMENT('["foo", 123, true]') SIMILAR DOCUMENT('["f_o", 123, true]');

-- TRUE
SELECT DOCUMENT('{"k":"%"}') SUBDOC DOCUMENT('{"k":"v1", "k2":"v2"}') ;