Implement JSON SQL functions
Implement 2 functions to 1) extract key(s) from a JSON document and 2) to replace values. These functions are the JSON equivalent to what ExtractValue() and UpdateXML() are for XML.
Blueprint information
- Status:
- Complete
- Approver:
- None
- Priority:
- Undefined
- Drafter:
- None
- Direction:
- Needs approval
- Assignee:
- None
- Definition:
- Obsolete
- Series goal:
- None
- Implementation:
- Unknown
- Milestone target:
- None
- Started by
- Completed by
- Henrik Ingo
Related branches
Related bugs
Sprints
Whiteboard
Got feedback from Roland Bouman that a "query syntax" is best implemented simply by allowing users to use javascript. I agree. Will post new blueprint for implementing js_eval(js_code, json_doc)
Marking this one obsolete.
****
Implement 2 functions that do the following:
json_fragment JSON_EXTRACT(
json_doc JSON_REPLACE(
*******
Examples:
*******
SET @jsontext = '{ "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
},
{ "category": "fiction",
"author": "Herman Melville",
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"price": 8.99
},
{ "category": "fiction",
"author": "J. R. R. Tolkien",
"title": "The Lord of the Rings",
"isbn": "0-395-19395-8",
"price": 22.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}'
SELECT JSON_EXTRACT(
author
---------
Nigel Rees
SELECT JSON_EXTRACT(
price
---------
8.95
12.99
8.99
22.99
SELECT JSON_REPLACE(
new_json_doc
----------
{ "store": {
"book": [
{ "category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 0.99
},
{ "category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 0.99
},
...
],
"bicycle": {
"color": "red",
"price": 0.99
}
}
}
*******
Decide on query language
*******
Above examples use a proposed JSON query language called JSONPath [5]. This is not necessarily the right way to go. A part of this task is to research alternatives and choose the right one. One should consider at least MQL [3], [4] or "whatever MongoDB uses" as strong alternatives.
Using a query notation that itself is JSON seems to have the strong advantage that one can use the same parser for both the document and the query notation. (Unlike XPath or JSONPath.) Note that the same advantage re-occurs on the client side: a javascript developer may build the query as javascript objects. Otoh this may not be possible if you want to support full power of xpath such as "*" or "price<10" operations.
*******
Misc
*******
Note that Drizzle already comes with a JSON parser. See [1]. You should obviously reuse it.
Please ask Roland Bouman (MQL to SQL) for input on syntax and functionality, in particular the selector syntax.
Ask MongoDB people for input (Flavio?)
Note that the functions proposed here would be used to manipulate JSON documents stored in a blob column, or in variables. This is not a mechanism for searching a table. The use case is same as ExtractValue() and UpdateXML() are for XML blobs [2].
References:
[1] http://
[2] http://
[3] http://
[4] http://
[5] http://