How do I query JSON data in SQL?
Part IV Query JSON DataYou can query JSON data using a simple dot notation or, for more functionality, using SQL/JSON functions and conditions. You can create and query a data guide that summarizes the structure and type information of a set of JSON documents. Because JSON data is stored in the database using standard data types (VARCHAR2, BLOB, and CLOB), SQL queries work with JSON data the same as with any other database data. To query particular JSON fields, or to map particular JSON fields to SQL columns, you can use the SQL/JSON path language. In its simplest form a path expression consists of one or more field names separated by periods (.). More complex path expressions can contain filters and array indexes. Oracle provides two ways of querying JSON content:
Because the path language is part of the query language, no fixed schema is imposed on the data. This design supports schemaless development. A schema, in effect, gets defined on the fly at query time, by your specifying a given path. This is in contrast to the more usual approach with SQL of defining a schema (a set of table rows and columns) for the data at storage time. Oracle SQL condition json_equal does not accept a path-expression argument. It just compares two JSON values and returns true if they are equal, false otherwise. For this comparison, insignificant whitespace and insignificant object member order are ignored. For example, JSON objects are equal if they have the same members, regardless of their order. However, if either of two compared objects has one or more duplicate fields then the value returned by json_equal is unspecified. You can generate and query a JSON data guide, to help you develop expressions for navigating your JSON content. A data guide can give you a deep understanding of the structure and type information of your JSON documents. Data guide information can be updated automatically, to keep track of new documents that you add. See Also: Oracle Database SQL Language Reference for complete information about the syntax and semantics of the SQL/JSON functions and conditions
|