Json to Dotted Key Converter (Json-Dot-Notation)
Dot notation is designed for easy, general use and common use cases. Queries of JSON data that use dot-notation syntax return JSON values whenever possible.
The return value for a dot-notation query is always a string (data type VARCHAR2(4000)) representing JSON data. The content of the string depends on the targeted JSON data, as follows:
- If a single JSON value is targeted, then that value is the string content, whether it is a JSON scalar, object, or array.
- If multiple JSON values are targeted, then the string content is a JSON array whose elements are those values.
This behavior contrasts with that of SQL/JSON functions json_value and json_query, which you can use for more complex queries. They can return NULL or raise an error if the path expression you provide them does not match the queried JSON data. They accept optional clauses to specify the data type of the return value (RETURNING clause), whether or not to wrap multiple values as an array (wrapper clause), how to handle errors generally (ON ERROR clause), and how to handle missing JSON fields (ON EMPTY clause).
In the first case above, the dot-notation behavior is similar to that of function json_value for a scalar value, and it is similar to that of json_query for an object or array value. In the second case, the behavior is similar to that of json_query with an array wrapper.
The dot-notation syntax is a table alias (mandatory) followed by a dot, that is, a period (.), the name of a JSON column, and one or more pairs of the form . json_field or . json_field followed by array_step, where json_field is a JSON field name and array_step is an array step expression as described in Basic SQL/JSON Path Expression Syntax.
Each json_field must be a valid SQL identifier,Foot 1 and the column must have an is json check constraint, which ensures that it contains well-formed JSON data. If either of these rules is not respected then an error is raised at query compile time. (The check constraint must be present to avoid raising an error; however, it need not be active. If you deactivate the constraint then this error is not raised.)
For the dot notation for JSON queries, unlike the case generally for SQL, unquoted identifiers (after the column name) are treated case sensitively, that is, just as if they were quoted. This is a convenience: you can use JSON field names as identifiers without quoting them. For example, you can write jcolumn.friends instead of jcolumn."friends". This also means that if a JSON object is named using uppercase, such as FRIENDS, then you must write jcolumn.FRIENDS, not jcolumn.friends.