JSON has become the de facto light weight data interchange format for many web applications. It has a simple hierarchical structure and supports a limited set of value types. JSON is a natural representation of data for the C family of programming languages.
JSON is supported in MonetDB as a subtype over type VARCHAR, which ensures that only valid JSON strings are added to the database.
CREATE TABLE json_example (c1 JSON, c2 JSON(512) NOT NULL);
MonetDB supports most of the JSON path expressions. It can be used to decompose the values into regular tables, which then act as an index. A limited set of operators are predefined in the SQL catalogue.
json.filter(J, Pathexpr) Extracts the component from J that satisfied the Pathexpr
json.filter(J, Number) Extracts a indexed component from J
json.text(J, [Sep]) Glue together the values separated by Sep character (default space)
json.number(J) Turn a number, singleton array value, or singleton object tag into a double
json."integer"(J) Turn a number, singleton array value, or singleton object element into an integer
json.isvalid(StringExpr) Checks the string for JSON compliance. Returns boolean.
json.isobject(StringExpr) Checks the string for JSON object compliance. Returns boolean.
json.isarray(StringExpr) Checks the string for JSON array compliance. Returns boolean.
json.length(J) Returns the number of top-level components of J.
json.keyarray(J) Returns a list of key tags for the top-level components of J.
json.valuearray(J) Returns a list of values for the top-level components of J.
JSON path expressions always refer to a single JSON structure. The root of this structure is identified by the identifier '$', which is implicitly assumed in most expressions. Components of the JSON structure are addressed through the dot notation, i.e. representing child steps and array element access. The wild card can be used for child names and undetermined array indices.
JSON path Description Example
"$" The root object json.filter(v, '$') = { "store":...}"
"." childname The child step operator json.filter(v, '$.store.bicycle') = {"color": "red", "price": 19.95}
".." childname Recursive child step json.filter(v, '$..price') = [8.95,12.99,8.99,22.99,19.95]
"*" Child name wildcard json.filter(v, '$.store.bicycle.*') = {"color": "red", "price": 19.95}
"[" nr "]" Array element access json.filter(v, '$.store.book.[1]') = the second book
"[" * "]" Any array element access
E1 "," E2 Union path expressions json.filter(v, '$.store.bicycle,$..price')
An example JSON object used for these expressions ref. More examples in the test-suite.
{ "store": {
"book": [
{ "category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{ "category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}