JSON_INCLUDE/EXCLUDE_MASK

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

JSON_INCLUDE_MASK(<json>,<mask>)

SQL

JSON_EXCLUDE_MASK(<json>,<mask>)

The <mask> must be a JSON document with zero or more fields. The values of each field is a nested mask or a literal integer 1. Valid example inputs are:

SQL

{}
{"a":1}
{"a":{"b":1}}
{"a":{"b":1,"c":{"d":1}},"e":1}

Mask Behavior

JSON_INCLUDE_MASK

All portions of the document that do not match the mask are eliminated. For example, if a is not an array, match logic applies only to a.
For nested fields, such as {"a":{"b":1}}, if a is an array, it will be included, and match logic will apply to each element in the array.
The match logic states: if the value is not a document, it is not included. However, if the value is a document, it will be included and applied to the rest of the document's mask {"b":1}.

JSON_EXCLUDE_MASK

All portions of the document that match the mask are eliminated.
For example, using {"a":1}, all fields except field a from the input document will remain.
For nested fields, such as {"a":{"b":1}}, the elimination behavior only applies to a which does not get excluded.

Arguments

json: JSON input must support anything, (i.e., a literal, a column reference, an expression, etc.).
mask: JSON input (must be a literal string representing the mask).

Remarks

If the <json> is NULL, it will return NULL.
If the <json> is not a document, it will return NULL.
If the <mask> is not a valid JSON literal string, it will return an error.
If the <mask> does not follow the correct format, it will return an error.

Return Value

JSON_INCLUDE_MASK all portions of the document that do not match the mask are eliminated.
JSON_EXCLUDE_MASK all the portions of the document that match the mask are eliminated.

Examples

All the examples utilize the table format and values shown below:

SQL

CREATE TABLE jmask(col_1 json);

INSERT INTO jmask VALUES('{"alpha":{"xi":1,"y":1},"beta":[{"zeta":"abc", "w":true}]}');
INSERT INTO jmask VALUES('{"alpha":{"xi":2,"y":3},"beta":[{"w":false}]}');
INSERT INTO jmask VALUES('{"alpha":{"xi":2},"beta":[]}');
INSERT INTO jmask VALUES('{"alpha":{"xi":2,"y":3},"beta":[{"w":false}, {"zeta":"cde"}]}');

SELECT * FROM jmask;

+--------------------------------------------------------------+
| col_1                                                        |
+--------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[]}                                 |
| {"alpha":{"xi":1,"y":1},"beta":[{"w":true,"zeta":"abc"}]}    |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false}]}                |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false},{"zeta":"cde"}]} |
+--------------------------------------------------------------+

Example 1

The results include both the alpha and beta fields along with their subdocuments.

SQL

SELECT JSON_INCLUDE_MASK(col_1,'{"alpha":1,"beta":1}') FROM jmask;

+--------------------------------------------------------------+
| JSON_INCLUDE_MASK(col_1, '{"alpha":1, "beta":1}')            |
+--------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[]}                                 |
| {"alpha":{"xi":1,"y":1},"beta":[{"w":true,"zeta":"abc"}]}    |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false}]}                |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false},{"zeta":"cde"}]} |
+--------------------------------------------------------------+

Example 2

The results include only the xi sub-field from the alpha top-level field. So the y sub-field will be omitted. The same applies to the beta field and each sub-document in the array.

SQL

SELECT JSON_INCLUDE_MASK(col_1,'{"alpha":{"xi":1},"beta":{"w":1}}') 
FROM jmask;

+--------------------------------------------------------------+
| JSON_INCLUDE_MASK(col_1,'{"alpha":{"xi":1},"beta":{"w":1}}') |
+--------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[]}                                 |
| {"alpha":{"xi":1},"beta":[{"w":true}]}                       |
| {"alpha":{"xi":2},"beta":[{"w":false}]}                      |
| {"alpha":{"xi":2},"beta":[{"w":false},{}]}                   |
+--------------------------------------------------------------+

Example 3

For JSON_INCLUDE_MASK, all portions of the document that do not match the mask are eliminated. For example, if alpha is not an array, match logic will only apply to alpha.

SQL

SELECT JSON_INCLUDE_MASK(col_1,'{"alpha":1}') FROM jmask;

+-----------------------------------------+
| JSON_INCLUDE_MASK(col_1, '{"alpha":1}') |
+-----------------------------------------+
| {"alpha":{"xi":1,"y":1}}                |
| {"alpha":{"xi":2}}			  |
| {"alpha":{"xi":2,"y":3}}                |
| {"alpha":{"xi":2,"y":3}}                |
+-----------------------------------------+

Example 4

For nested fields, such as {"alpha":{"beta": 1}}, if alpha is an array, it will be included and match logic will apply to each element in the array.

The match logic states: if the value is not a document, it is not included. But, if the value is a document, it will be included and applied to the rest of the document’s mask {"beta":1}.

SQL

SELECT JSON_INCLUDE_MASK(col_1,'{"alpha":{"beta":1}}') FROM jmask;

+-------------------------------------------------+
| JSON_INCLUDE_MASK(col_1,'{"alpha":{"beta":1}}') |
+-------------------------------------------------+
| {"alpha":{}}                                    |
| {"alpha":{}}                                    |
| {"alpha":{}}                                    |
| {"alpha":{}}                                    |
+-------------------------------------------------+

Example 5

Results exclude the y sub-field from the top-level alpha field and the w sub-field from the top-level beta field. Since the value of the beta field is an array, this will apply to each sub-document in the array.

SQL

SELECT JSON_EXCLUDE_MASK(col_1,'{"alpha":{"y":1},"beta":{"w":1}}') 
FROM jmask;

+---------------------------------------------------------------+
| JSON_EXCLUDE_MASK(col_1, '{"alpha":{"y":1}, "beta":{"w":1}}') |
+---------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[]}                                  |
| {"alpha":{"xi":1},"beta":[{"zeta":"abc"}]}                    |
| {"alpha":{"xi":2},"beta":[{}]}                                |
| {"alpha":{"xi":2},"beta":[{},{"zeta":"cde"}]}                 |
+---------------------------------------------------------------+

Example 6

For JSON_EXCLUDE_MASK, all portions of the document that match the mask are eliminated.

For example, using {"alpha":1}, all fields except the field alpha from the input document will remain

SQL

SELECT JSON_EXCLUDE_MASK(col_1, '{"alpha":1}') FROM jmask;

+-----------------------------------------+
| JSON_EXCLUDE_MASK(col_1, '{"alpha":1}') |
+-----------------------------------------+
| {"beta":[]}                             |
| {"beta":[{"w":false}]}                  |
| {"beta":[{"w":false},{"zeta":"cde"}]}   |
| {"beta":[{"w":true,"zeta":"abc"}]}      |
+-----------------------------------------+

Example 7

For nested fields, such as {"alpha":{"beta":1}}, the elimination behavior only applies to alpha which does not get excluded.

SQL

SELECT JSON_EXCLUDE_MASK(col_1,'{"alpha":{"beta":1 }}') FROM jmask;

+--------------------------------------------------------------+
| JSON_EXCLUDE_MASK(col_1,'{"alpha":{"beta":1}}')              |
+--------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[]}                                 |
| {"alpha":{"xi":1,"y":1},"beta":[{"w":true,"zeta":"abc"}]}    |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false}]}                |
| {"alpha":{"xi":2,"y":3},"beta":[{"w":false},{"zeta":"cde"}]} |
+--------------------------------------------------------------+

Example 8

Both include and exclude may be used together. For example, both the xi subfields from alpha's top-level fields and beta with its nested fields are included. The w subfield from the beta field is excluded.

SQL

SELECT JSON_EXCLUDE_MASK(JSON_INCLUDE_MASK(col_1,'{"alpha":{"xi":1},"beta":1}'),'{"beta":{"w":1}}') FROM jmask;

+----------------------------------------------------------------------------------------------+
| JSON_EXCLUDE_MASK(JSON_INCLUDE_MASK(col_1,'{"alpha":{"xi":1},"beta":1}'),'{"beta":{"w":1}}') |
+----------------------------------------------------------------------------------------------+
| {"alpha":{"xi":2},"beta":[{}]}                                                               |
| {"alpha":{"xi":2},"beta":[{},{"zeta":"cde"}]}                                                |
| {"alpha":{"xi":1},"beta":[{"zeta":"abc"}]}                                                   |
| {"alpha":{"xi":2},"beta":[]}                                                                 |
+----------------------------------------------------------------------------------------------+