Skip to main content

JSON functions

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]

 

Aggregate functions returning a concatenated CLOB string which is a valid json arrary

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" ]

See also: AggregateFunctions