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.
On this page
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.
{}{"a":1}{"a":{"b":1}}{"a":{"b":1,"c":{"d":1}},"e":1}
Mask Behavior
JSON_
-
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_
-
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_
all portions of the document that do not match the mask are eliminated.INCLUDE_ MASK -
JSON_
all the portions of the document that match the mask are eliminated.EXCLUDE_ MASK
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.
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_
, all portions of the document that do not match the mask are eliminated.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.{"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.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_
, 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.xi
subfields from alpha's
top-level fields and beta
with its nested fields are included.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":[]} |
+----------------------------------------------------------------------------------------------+
Last modified: May 5, 2023