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 toa
.For nested fields, such as
{"a":{"b":1}}
, ifa
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 fielda
from the input document will remain.For nested fields, such as
{"a":{"b":1}}
, the elimination behavior only applies toa
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":[]} | +----------------------------------------------------------------------------------------------+