Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.

SQL/JSON path expressions are somewhat analogous to XQuery or XPath expressions for XML data. They provide SQL access to JSON data similarly to how SQL/XML allows SQL access to XML data using XQuery expressions.

SQL/JSON path expressions have a simple syntax. A path expression selects zero or more JSON values that match, or satisfy, it.

SQL/JSON condition json_exists returns true if at least one value matches, and false if no value matches. If a single value matches, then SQL/JSON function json_value returns that value if it is scalar, and raises an error if it is nonscalar. If no value matches the path expression then json_value returns SQL NULL.

SQL/JSON function json_query returns all of the matching values, that is, it can return multiple values. You can think of this behavior as returning a sequence of values, as in XQuery, or you can think of it as returning multiple values. (No user-visible sequence is manifested.)

In all cases, path-expression matching attempts to match each step of a path expression, in turn. If matching any step fails then no attempt is made to match the subsequent steps, and matching of the path expression fails. If matching each step succeeds then matching of the path expression succeeds.

The maximum length of the text of a SQL/JSON path expression is 32K bytes. However, the effective length of a path expression is essentially unlimited, because the expression can make use of SQL/JSON variables that are bound to string values, each of which is limited to 32K bytes.