# JSON\_INCLUDE/EXCLUDE\_MASK The `JSON_INCLUDE|EXCLUDE_MASK` function applies an include/exclude mask to a JSON document so only a subset of the original document is returned. ## Syntax ```sql JSON_INCLUDE_MASK(,) ``` ```sql JSON_EXCLUDE_MASK(,) ``` The `` 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 `` is NULL, it will return NULL. * If the `` is not a document, it will return NULL. * If the `` is not a valid JSON literal string, it will return an error. * If the `` 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; ``` ```output +--------------------------------------------------------------+ | 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; ``` ```output +--------------------------------------------------------------+ | 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; ``` ```output +--------------------------------------------------------------+ | 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; ``` ```output +-----------------------------------------+ | 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; ``` ```output +-------------------------------------------------+ | 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; ``` ```output +---------------------------------------------------------------+ | 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; ``` ```output +-----------------------------------------+ | 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; ``` ```output +--------------------------------------------------------------+ | 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; ``` ```output +----------------------------------------------------------------------------------------------+ | 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":[]}                                                                 | +----------------------------------------------------------------------------------------------+ ``` *** Modified at: June 16, 2026 Source: [/db/v9.1/reference/sql-reference/json-functions/json-include-exclude-mask/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-include-exclude-mask/) (An index of the documentation is available at /llms.txt)