However, this basic syntax is extended by relaxing the matching of arrays and non-arrays against non-array and array patterns, respectively - see SQL/JSON Path Expression Syntax Relaxation.
If matching each step succeeds then matching of the path expression succeeds. If matching any step fails then no attempt is made to match the subsequent steps, and matching of the path expression fails. In all cases, path-expression matching attempts to match each step of the path expression, in turn. (No user-visible sequence is manifested.) 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. SQL/JSON function json_query returns all of the matching values, that is, it can return multiple values. If no value matches the path expression then json_value returns SQL NULL. 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 non-scalar. SQL/JSON condition json_exists returns true if at least one value matches, and false if no value matches. A path expression selects zero or more JSON values that match, or satisfy, it.
SQL/JSON path expressions have a simple syntax. Similar to the way that SQL/XML allows SQL access to XML data using XQuery expressions, Oracle Database provides SQL access to JSON data using SQL/JSON path expressions. When JSON data is stored in the database you can query it using path expressions that are somewhat analogous to XQuery or XPath expressions for XML data. JSON is a notation for JavaScript values. Q'#]. Database provides SQL access to JSON data using SQL/JSON path expressions.
Select json_value(body, '$.sections[#' || i || If in fact you are indeed only extracting two scalar values from a very large document, two calls to JSON_VALUE() may be justified, especially if the document is much larger than shown here but if you are extracting many scalar values from a document that is not too complicated, then a single call to JSON_TABLE() may be better, even if in the end you don't use all the data it produces.Īnyway - as an illustration of native dynamic SQL, here's the alternative solution (using MT0's table): declare Depending on the actual requirement (I assume the one in your question is just for illustration) and the size of the document, it may be more efficient to make a single call to JSON_TABLE(), as illustrated in MT0's answer. However, note that this approach makes repeated calls to JSON_VALUE() over the same document. PL/SQL has an answer for that - native dynamic SQL, as demonstrated below. So, the problem is that you want to loop over an index (to go diagonally through the array of arrays, picking up just two elements) - but the JSON_* functions don't work with variables as array indices - they require hard-coded indexes. (Note: the indexes from FOR ORDINALITY are 1 higher than the array indexes in the JSON path.) Or, you can just use a query: SELECT section_index, capsule_index, time INSERT INTO bodycontent ( body, contentid ) VALUES ( Which, for the test data: CREATE TABLE bodycontent ( body CLOB CHECK ( body IS JSON ), contentid NUMBER )
0 kommentar(er)
