These apply to MonetDB SQL type: JSON (JavaScript Object Notation).
Note: All JSON functions are located in the json schema. You must include the json. prefix for all these functions in order to work properly.
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| json.filter(js, indx) | json | extracts a indexed component from js. The index starts at 0 for the first element. | json.filter(json '["a", 4]', 1) | [4] |
| json.filter(js, pathexpr) | json | extracts the compgonent from js that satisfied the pathexpr string. The supported JSON path expression syntax is documented at JSONPath | json.filter(json '{"price":9}', '$..price') | [9] |
| json."integer"(js) | bigint or int or smallint or tinyint | turn a number or singleton array value or singleton object element of js into an integer. Returns null if it fails. | json."integer"(json '{"n":4}') | 4 |
| json.isarray(str) | boolean | checks the string str for JSON array compliance | json.isarray('[1,7]') | true |
| json.isarray(js) | boolean | checks the JSON value js for JSON array compliance | json.isarray(json '[1,7]') | true |
| json.isobject(str) | boolean | checks the string str for JSON object compliance | json.isobject('{"n":4}') | true |
| json.isobject(js) | boolean | checks the JSON value js for JSON object compliance | json.isobject(json '{"n":4}') | true |
| json.isvalid(str) | boolean | checks the string str for JSON syntax compliance | json.isvalid('{"a":[1]}') | true |
| json.isvalid(js) | boolean | checks the JSON value js for JSON validity | json.isvalid(json '{"a":[1,2]}') | true |
| json.keyarray(js) | json | returns an arraylist of key tags for the top-level components of js. Note js must represent a JSON object else an error (Object expected) is reported | json.keyarray(json '{"id":2, "nm":"X"}') | ["id","nm"] |
| json.length(js) | int | returns the number of top-level components of js | json.length(json '{"a":[1]}') | 1 |
| json.number(js) | double | turn a number or singleton array value or singleton object tag of js into a double. Returns null if it fails. | json.number(json '{"n":4}') | 4.0 |
| json.text(js) | clob | glue together the values in js separated by space character | json.text(json '[1,2,3,4]') | 1 2 3 4 |
| json.text(js, Separator str) | clob | glue together the values in js separated by Separator string | json.text(json '[1,2,3,4]', 'x') | 1x2x3x4 |
| json.valuearray(js) | json | returns an arraylist of value tags for the top-level components of js. Note js must represent a JSON object else an error (Object expected) is reported | json.valuearray(json '{"a":1, "b":2}') | [1,2] |
They return a concatenated CLOB string which is a valid json array
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| json.tojsonarray(double) | clob | convert numeric values in the column/group into a json array string | json.tojsonarray(i) | [ "1", "2", "3", "4" ] |
| json.tojsonarray(clob) | clob | convert string values in the column/group into a json array string | json.tojsonarray(c) | [ "one", "two", "tree", "four" ] |