JSON_INCLUDE/EXCLUDE_MASK

This function applies an include/exclude mask to a JSON document so only a subset of the original document is returned.

Syntax

JSON_INCLUDE_MASK(<json>,<mask>)

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:

{}
{"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:

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.

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.

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.

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}.

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.

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

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.

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.

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":[]}                                                                 |
+----------------------------------------------------------------------------------------------+

In this section: