X
FEATURED POST

JSON Generation: to quote or not to quote, that is the question

The last posting showed how JSON object and arrays can be generated from existing (relational ) data. The examples showed scalar column data being assembled...

Recent Posts

Generating JSON data

Previous entries in this blog have shown how you can store and query existing JSON data. Since Oracle Database release 12.2 we allow the creation of JSON data using 4 new SQL operators  JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG (which are all part of the new SQL 2016 standard). In this entry we will walk you thru the 4 operators and show you how a combination can create a complex JSON instance. But first the use cases; why would someone want to create JSON from (relational) data in the Oracle database? A typical use case expressed by customers is to web-enable a (legacy) relational application. In this case the data is stored relationally (aka in tables and columns) and a new browser-based application benefits if the data coming from the database is in JSON format. Instead of relying on some middleware code to turn the data into JSON wouldn't it be nicer (and faster) if the database could perform the conversion? Let's take the following as as example: an very simple model of a purchasing system. Customers can submit orders and orders consists of one or multiple items from a catalog. This is the Entity-Relationship-Diagram which shows the attributes with a few attributes:    The following tables are used to reflect this ER-diagram; for the sake of simplicity I omit primary/foreign keys relationships.   CREATE TABLE customers (     cust_id number,         first varchar2(30),       last varchar2(30),     join_date timestamp); insert into customers values (1, 'Eric', 'Cartman', SYSDATE); insert into customers values (2, 'Kenny', 'McCormick', SYSDATE); insert into customers values (3, 'Kyle', 'Brofloski', SYSDATE); insert into customers values (4, 'Stan', 'Marsh', SYSDATE);   CREATE TABLE items (     item_id number,      name    varchar2(30),       price   number(5,2),     stock_quantity number); insert into items values (122, 'Potato Gun', 29.99, 10); insert into items values (232, 'Rubber Christmas Tree', 65.00, 0); insert into items values (345, 'Border Patrol Costume', 19.99, 20); insert into items values (845, 'Meteor Impact Survival Kit', 299.00, 0); insert into items values (429, 'Air Guitar', 9.99, 14); commit;   CREATE TABLE orders (     order_id number,     cust_id number,      order_date timestamp); insert into orders values (1, 1, SYSDATE); insert into orders values (2, 1, SYSDATE); insert into orders values (3, 3, SYSDATE); insert into orders values (4, 2, SYSDATE);   CREATE TABLE lineitems (     order_id number,      item_id  number,     quantity number); insert into lineitems values(1, 845, 1); insert into lineitems values(2, 232, 1); insert into lineitems values(2, 429, 4); insert into lineitems values(3, 122, 1); insert into lineitems values(4, 345, 1);   Because of the normalization of the data some tables are hard to read; you need to follow the relationships expressed by the id's to find out the name of ordered items for example.  The want the JSON we generate from this data to be a bit more readable - so we will perform lookups.   1) Creating a JSON representation for every customer using JSON_OBJECT: SELECT JSON_OBJECT ('id2' VALUE cust_id,                                            'name' VALUE (first ||' '|| last),                                            'joined' VALUE join_date) FROM customers; -------------------------------------------------------------------------------- {"id2":1,"name":"Eric Cartman","joined":"2017-07-10T16:26:15"} {"id2":2,"name":"Kenny McCormick","joined":"2017-07-10T16:26:15"} {"id2":3,"name":"Kyle Brofloski","joined":"2017-07-10T16:26:15"} {"id2":4,"name":"Stan Marsh","joined":"2017-07-10T16:26:15"} The JSON_OBJECT functions takes a list of key/value pairs (separated by the keyword 'VALUE') and returns a JSON object for each row. The data field used the ISO-8601 data format.   2) A simple JSON array A similar function is JSON_ARRAY which creates one JSON array for every input row. SELECT JSON_ARRAY(first, last) FROM customers; -------------------------------------------------------------------------------- ["Eric","Cartman"] ["Kenny","McCormick"] ["Kyle","Brofloski"] ["Stan","Marsh"]   3) Create a JSON Array with surplus items using JSON_ARRAYAGG To create a JSON Array of variable size form multiple input rows we need an aggregate functions: JSON_ARRAYAGG. Here we create a JSON Array containing multiple JSON objects. Where therefore nest the two operators. The selection of surplus items happens in the WHERE clause of the query by comparing the 'stock_quantity' value with a constant (>10). SELECT JSON_ARRAYAGG(           JSON_OBJECT('id' VALUE item_id,                       'name' VALUE name,                       'quantity' VALUE stock_quantity))  FROM items WHERE stock_quantity > 10; -------------------------------------------------------------------------------- [{"id":345,"name":"Border Patrol Costume","quantity":20},{"id":429,"name":"Air Guitar","quantity":14}]   4) JSON form multiple tables Now, nesting the JSON generation functions allow us to create one JSON instance that reflects the state of all four tables; this means that we need to join the four tables - again, a strength of a hybrid system that provides you native JSON processing without sacrificing relational features likes join. Also, SQL allows us to do calculations, like counting the number of orders and multiplying the price of a single item with its quantity. We use subqueries with join conditions in the WHERE clause to aggregate the information. The number of rows is driven by the 'customers' table, then for each customer we query the other three table to find the orders and the prices and names of the ordered items: SELECT JSON_OBJECT(           'id' VALUE c.cust_id,            'name' VALUE (c.first || ' ' || c.last),           'num_orders' VALUE (                SELECT COUNT(*)                FROM orders o                WHERE o.cust_id = c.cust_id),           'orders' VALUE (                SELECT JSON_ARRAYAGG(                     JSON_OBJECT(                          'order_id' VALUE o.order_id,                            'date' VALUE o.order_date,                          'items' VALUE (                               SELECT JSON_ARRAYAGG (                                    JSON_OBJECT(                                         'id' VALUE l.item_id,                                           'name' VALUE i.name,                                         'quantity' VALUE l.quantity,                                         'single_item_price' VALUE i.price,                                         'total_price' VALUE (i.price * l.quantity)))                                FROM lineitems l, items i                                WHERE l.order_id = o.order_id                                AND i.item_id = l.item_id)))           FROM orders o           WHERE o.cust_id = c.cust_id) ABSENT ON NULL) FROM customers c; -------------------------------------------------------------------------------- {"id":1,"name":"Eric Cartman","num_orders":2,"orders":[{"order_id":1,"date":"2017-07-10T16:26:15","items":[{"id":845,"name":"Meteor Impact Survival Kit","quantity":1,"single_item_price":299,"total_price":299}]},{"order_id":2,"date":"2017-07-10T16:26:15","items":[{"id":232,"name":"Rubber Christmas Tree","quantity":1,"single_item_price":65,"total_price":65},{"id":429,"name":"Air Guitar","quantity":4,"single_item_price":9.99,"total_price":39.96}]}]} {"id":2,"name":"Kenny McCormick","num_orders":1,"orders":[{"order_id":4,"date":"2017-07-10T16:26:15","items":[{"id":345,"name":"Border Patrol Costume","quantity":1,"single_item_price":19.99,"total_price":19.99}]}]} {"id":3,"name":"Kyle Brofloski","num_orders":1,"orders":[{"order_id":3,"date":"2017-07-10T16:26:15","items":[{"id":122,"name":"Potato Gun","quantity":1,"single_item_price":29.99,"total_price":29.99}]}]} {"id":4,"name":"Stan Marsh","num_orders":0}   (to pretty print these long JSON results use a tool like JSON-Lint)   Null handling You see an 'ABSENT ON NULL' in a JSON_OBJECT function above. It specifies how a NULL value is handled. By default a NULL value is omitted in JSON_ARRAY but not omitted in JSON_OBJECT. In the example we want to avoid printing an empty array for customers who have not ordered anything. This is why the record for Stan has no  key 'orders'. The other option is 'NULL ON NULL' which would have created a 'null' value for the 'orders' field.   5) Create a Hashmap of users with JSON_OBJECTAGG Let's assume we want to retrieve n object containing the customers id's as keys and their corresponding name as value. This is again an aggregation since multiple input rows (one per customer) lead to the creation of one output (the JSON object).   SELECT JSON_OBJECTAGG(to_char(cust_id) VALUE (first ||' '|| last)) FROM customers; -------------------------------------------------------------------------------- {"1":"Eric Cartman","2":"Kenny McCormick","3":"Kyle Brofloski","4":"Stan Marsh"}   JSON_OBJECTAGG allows only exactly one key-value-pair. And the keys should be unique, otherwise the same key is associated with multiple values.     Find out more? Leave a comment or read more about the JSON generation functions in Oracle here: https://docs.oracle.com/database/122/ADJSN/generation.htm

Previous entries in this blog have shown how you can store and query existing JSON data. Since Oracle Database release 12.2 we allow the creation of JSON data using 4 new SQL operators  JSON_OBJECT,...

Interview @Oracle Openworld

got interviewed by Laura Ramsey at the Oracle Open World; Speaking about JSON in the database, JSON DataGuide and the SODA (Simple Oracle Document Access) interface. see for yourself .... https://www.youtube.com/watch?v=1VxgmAlaA_4 

got interviewed by Laura Ramsey at the Oracle Open World; Speaking about JSON in the database, JSON DataGuide and the SODA (Simple Oracle Document Access) interface. see for yourself .... https://www.yout...

Oracle Open World/Java One 2016: visit us!

You have multiple opportunities to learn about JSON and SODA at this year's open world: If you like hands on experience consider visiting the 'hand on lab'. There are 4 slots (same content)  Using Oracle Database 12c as a NoSQL JavaScript Object Notation Document Store [HOL7441]Mark Drake, Product Manager, Oracle Monday, Sep 19, 11:00 a.m. - 12:00 p.m. | Hotel Nikko - Golden Gate (25th Floor) Tuesday, Sep 20, 8:00 a.m. - 9:00 a.m. | Hotel Nikko - Golden Gate (25th Floor) Wednesday, Sep 21, 8:00 a.m. - 9:00 a.m. | Hotel Nikko - Golden Gate (25th Floor) Thursday, Sep 22, 8:00 a.m. - 9:00 a.m. | Hotel Nikko - Golden Gate (25th Floor) We have two talks, both addressing developers, one at Open World, the other at JavaOne Modern Application Development: JavaScript Object Notation, REST, Node.js, and Oracle Database [CON6504]Mark Drake, Product Manager, OracleZhen Hua Liu, Architect, OracleMonday, Sep 19, 4:15 p.m. - 5:00 p.m. | Park Central - Franciscan I Instant Search-Enabled Apps with Oracle Database as a JSON Document Store [CON6772](This talks presents the SODA Document API)Beda Hammerschmidt, Consulting Member of Technical Staff, OracleMaxim Orgiyan, PMTS, OracleWednesday, Sep 21, 11:30 a.m. - 12:30 p.m. | Hilton - Golden Gate 2/3 Open discussion @ Meet the Experts: Oracle XML Database [MTE7225]Mark Drake, Product Manager, OracleBeda Hammerschmidt, Consulting Member of Technical Staff, OracleSriram Krishnamurthy, Consulting Member of Technical Staff, OracleVikas Arora, Senior Director, OracleGeeta Arora, Director of Development, OracleTuesday, Sep 20, 7:15 p.m. - 8:00 p.m. | Moscone South - 307 Demo Booth SDB I 009 you can find us on the Database Demo Grounds in Moscone South at booth SDB I 009 . . .  There are also (at least) two customer talks @ Open World.The titles sound interesting, so I mention them here:  Read, Store, and Create XML and JavaScript Object Notation [UGF3572] Kim Berg Hansen, Senior Developer, Trivadis Danmark A/SSunday, Sep 18, 9:15 a.m. - 10:00 a.m. | Moscone South - 302 Design Flexible Data Models Using Oracle's JavaScript Object Notation Capabilities [CON1834] Aris Prassinos, Chief Engineer, MorphoThursday, Sep 22, 12:00 p.m. - 12:45 p.m. | Park Central - Olympic

You have multiple opportunities to learn about JSON and SODA at this year's open world: If you like hands on experience consider visiting the 'hand on lab'. There are 4 slots (same content)  Using...

JSON and date/timestamp values

We have been asked repeatedly by customers to share our ideas and recommendations on how to present dates in JSON. JSON's type system has no build in data types for dates. So what are our options?  0) Ideally one would want JSON to support dates natively by the use of some new syntax in JSON - something like {"birthdate" : #date 2015/12/05 } Currently this example is syntactically invalid JSON and it looks like JSON won't change - requests from our side got us a "JSON will never change..." response. Such change would also require every JSON parser to be changed and accept this new syntax. Not gonna happen.  This leaves us to use existing data types to express date and timestamp values. 1) Numbers. MongoDB uses long integers as well as a markup schema to indicate that a value is a date.: {"birthday": {"$.date": 1449273600000}} The annotation scheme ($.date) is called eJSON (https://www.meteor.com/ejson) and seems to be a good idea since it also supports other data types that are not native to JSON. Traction by now is unclear though. A JSON parser would have to understand the annotation, i.e. that "$date" is followed by something that represents the date. But using numbers to express a date (more precisely the seconds since 1970) does not seem like a very good idea:  values are not human readable  every date is a time stamp, there is no coarser granularity (and no finer, you cannot express microseconds)  some dates require a negative number, if you're born before 1970 your birthday is a negative number 3) This brings us to strings. Obviously every date value can undergo some serialization process and can thus be converted to a string. Question is what format should be used. Oracle users are used to have a date (and number) format that reflects their locality (NLS settings). In Germany (all of Europe?) the day comes before the month, in the US it is the opposite. Thus 2/1/15 is the second Jan in Germany whereas it is the first Feb in the US. Confusing. Adding a second key/value pair for the format does not seem like a good idea either since the date value would not be self-contained anymore - it would be meaningless without the format value. The conclusion can only be to not support different date formats in JSON! There can only be one format in order to be understood globally by different systems. This brings us to ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601). Using the ISO 8601 has many benefits  no ambiguities by different formats  string representation can be read and understood by humans  different granularities are possible (day, day and time, timestamp, etc)  Date and time values are organized from the most to the least significant - this allows lexicographic sorting. Your 'order by' on a JSON_VALUE VARCHAR2 column works out of the box.  Oracle database users can perform a 'to_date' conversion to get an Oracle date and use NLS to print it with the right locality settings (if needed) Thus, our recommendation is: use only ISO 8601 to represent dates in JSON. comments welcome.

We have been asked repeatedly by customers to share our ideas and recommendations on how to present dates in JSON. JSON's type system has no build in data types for dates. So what are our options?  0)...

Visit us at Open World

If you plan to attend Oracle Open World in San Francisco (https://www.oracle.com/openworld) consider visiting these presentations around JSON/ Document Store / REST. Talks:  JSON and SQL: A Roadmap to Zenful Application Development [CON8745] Wednesday, Oct 28, 11:00 a.m. | Moscone South—309 Schemaless Application Development with Simple Oracle Document Access API [CON8744]Thursday, Oct 29, 1:15 p.m. | Moscone South—309 Developing Content-Rich Applications with Oracle Text 12.2 [CON8751](JSON capabilities of Oracle Text Index)Thursday, Oct 29, 12:00 p.m. | Moscone South—309 Hands On Lab (actively work/play with product) An Introduction to Using Oracle Database 12c as a NoSQL JSON Document Store [HOL10446]Monday, Oct 26, 2:00 p.m. | Hotel Nikko—Bay View (25th Floor)Tuesday, Oct 27, 11:45 a.m. | Hotel Nikko—Bay View (25th Floor)Wednesday, Oct 28, 4:15 p.m. | Hotel Nikko—Bay View (25th Floor)Thursday, Oct 29, 11:00 a.m. | Hotel Nikko—Bay View (25th Floor) Discussions Meet the Experts: Oracle XML DB  (XML and JSON)Tuesday, Oct 27, 7:15 p.m. | Moscone South—306 Demo Booths/Exhibitions Oracle Database JSON Store and Oracle XML DBWorkstation:SLD-055Moscone South, Upper Left, Database Oracle REST Data ServicesWorkstation:SLD-002Moscone South, Upper Left, Database

If you plan to attend Oracle Open World in San Francisco (https://www.oracle.com/openworld) consider visiting these presentations around JSON/ Document Store / REST. Talks:  JSON and SQL: A Roadmap to...

Try out examples with LiveSQL

Mot of the blog postings here have sample data and SQL code ready to be copied/pasted to SQL Developer or SQL/Plus. Still, one needs access to an Oracle Database instance (Release 12.1.0.2 or later). Of course one can always download and install the database (free for developers) but there is an ever quicker way to play with the examples: Oracle Live SQL  Live SQL gives you access to an Oracle Database thru a web browser - no installation, etc required. Check it out here: https://livesql.oracle.com

Mot of the blog postings here have sample data and SQL code ready to be copied/pasted to SQL Developer or SQL/Plus. Still, one needs access to an Oracle Database instance (Release 12.1.0.2 or later)....

Using REST to access JSON data in the Oracle Database (SODA for REST)

SODA for REST provides features similar to SODA for Java (covered in a previous posting: https://blogs.oracle.com/jsondb/entry/oracle_rdbms_as_a_nosql):  JSON data (documents) are stored in collections (each collection backed by an automatically created and maintained Oracle Database table) documents are identified by IDs on document insert and replace, additional metadata is created or updated (ID, creation timestamp, last modified timestamp, eTag) queries can be expressed without SQL. Instead queries are expressed as ‘Query by Example (QBE)’ documents in JSON format The goal of the SODA API (for Java and REST) is to make developing applications faster and easier while still enjoying all the benefits of the Oracle Database.   SODA for REST is usable from any programming language, as long as it allows HTTP requests to be made (e.g. JavaScript, Python, etc.). In this first posting we will use the Chrome extension ‘Postman’ to make calls to the database. You could also use ‘c url’. SODA for REST requires Oracle Database patch 20885778 to be installed. Obtain the patch from My Oracle Support (https://support.oracle.com). Select tab Patches & Updates. Search for the patch number, 20885778 or access it directly at this URL: https://support.oracle.com/rs?type=patch&id=20885778. SODA for REST ships with ORDS 3.0 (Oracle Rest Data Services): http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html First we need to install ORDS. Download the ORDS file and unzip it into a new folder. In that folder you should see a file called ‘ords.war’: We will install ORDS using SQLDeveloper. If you do not have SQL Developer, then please download it from here: http://www.oracle.com/technetwork/developer-tools/sql-developer . You can also install ORDS outside of SQLDeveloper (without a GUI), see here for instructions: http://docs.oracle.com/cd/E56351_01/doc.30/e58123/rest.htm#ADRST107 . In SQLDeveloper, in the menu ‘Tools’, you will find the option ‘REST Data Services’. Click Install: Point the installer to the ‘ords.war’ file that you justdownloaded and unzipped. Point the configuration directory to the folder thatcontains ‘ords.war’. Provide the connection information to the database. Providea password for the ORDS_PUBLIC_USER schema (e.g. ORDS): Do NOT install the PL/SQL gateway. Also, APEX REST Services are not needed (we want to use ORDSREST services instead). Define the port where the REST service is listening: No need to create database users for an ORDS administratoror RESTful services user. Finish the installation. (The installation process may request you toauthenticate as a user with the SYSDBA role in order to install the ORDSschema.) Click ‘Finish’ and you should see the ORDSserver outputting log information into the window at the bottom when it starts: Now, ORDS is installed (on the localhost), and the server is listening (on the port you configured, 8080 in the above example) for requests. Before we can use the SODA for REST service, we need to (create and) activate a database schema for it.  You can pick an existing schema (e.g. SCOTT) or create a new one.  Connect to the schema and then execute a PL/SQL command to enable it for use with ORDS: conn scott/tiger@localhost:1521/pdborcl SQL> exec ords.enable_schema PL/SQL procedure successfully completed. Also, we disable security (authentication, etc) in order to show simple REST requests without adding security related complexity. Note: disabling security in a production environment is not recommended! SQL> beginords.delete_privilege_mapping('oracle.soda.privilege.developer','/soda/*');end; / PL/SQL procedure successfully completed. You can now test the installation of ORDS by making a request to the server using your Postman (or another tool that can make HTTP calls).  The url is http://localhost:8080/ords/scott/soda/latest/If you have used a different schema than ‘scott’ then replace it with your schema name (in lower case).  If you see JSON being returned in response to the request than the installation was successful. In case you wonder what this JSON output tells us: it’s a list of all collections that exists in that schema. Since we do not have any collections yet, the array ‘items’ is empty. Now, let’s create a new collection, called ‘customers’. We do this with a PUT request, here performed using Postman. After this, the same URL we invoked before (to list all collections), will show our newly added collection called customers: What’s returned here is collection metadata for the newly created collection. As you can see from this particular metadata, the collection maps to a table with the name “customers”, and JSON data is stored in a BLOB column. Columns are included to store an auto-generated version, last-modified timestamp, and creation-time. Collection metadata is highly configurable, though that’s a topic for another posting. Here, the default collection metadata settings are used. Adding a document is a POST operation to the very same url that we used to create the collection. This time we get a response which tells us the ID that was used to store the new document. We also get ‘eTag’, which can be used for caching to identify changed documents, as well as the last-modified and creation timestamps. Finding a document by ID is a GET request withthe ID following the collection name: Deleting a document is asimple DELETE request to the same URL http://localhost:8080/ords/scott/soda/latest/customers/C4C0208AAA1842F588C781A02484DBA7 (note: the trailing part of the URL starting with "C4C0..." will be different in your case, since the auto-generated ID, based on a UUID, will be different). Now, how can we retrieve one (or more) documents based on some properties (e.g. all customers having the same zip code)?We use a QBE (Query By Example), which is itself a JSON document that defines what we’re interested in. Given the JSON customer data we’ve been using in this posting, to find all JSONs representing customers with the same zip code, the QBE is: {"address.zip": 94065} A POST request to collection url with the parameter ‘action=query’ and a QBE as the body allows us to query all documents in the collection: The result is an array of the items (circled in red) that match the supplied QBE. In this case, the array contains only one item (circled in green), since the QBE matches the only item we have in the collection. The result also contains information (circled in blue) about the number of items in the array and if there are more (which allows for result set pagination, though that’s a topic for another blog posting). More complex QBEs can be expressed by adding constructs like ‘$and’, ‘$or’,’$in’, etc to the JSON representing the QBE. What are some other interesting QBEs we can run, assuming more documents with similar structure in the collection? To find customers in a list of different zip codes the QBE is:  {    "address.zip": {"$in":[94065, 94070]}} A range query can be expressed with: {    "address.zip": {"$gt":94000,  "$lt":95000}} A query on multiple properties can be expressed with Boolean AND, OR: {    "$or": [        {            "first": "Beda"        },        {            "address.zip": 94000        }    ]} As you can see predicates can be nested arbitrarily using logical expressions. In case no document matches the QBE you will get this response to your query: Since the data is stored in a regular Oracle Database table (automatically managed by SODA for REST) one can easily use SQL to query it directly. For instance, you can run reports with SQL (using JSON_TABLE), or perform joins with existing relational tables. The document/collection API just provides a level of abstraction over Oracle Database tables to enable the ease of use of a NoSQL document store. You can still drop down into SQL if needed. If you want to read more: SODA for REST documentation: https://docs.oracle.com/cd/E56351_01/doc.30/e58123/toc.htm SODA for JAVA documentation: http://docs.oracle.com/cd/E63251_01/doc.12/e58124/soda.htm Oracle REST data services (ORDS): http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

SODA for REST provides features similar to SODA for Java (covered in a previous posting: https://blogs.oracle.com/jsondb/entry/oracle_rdbms_as_a_nosql):  JSON data (documents) are stored in collections...

Some comments on streaming evaluation, error handling and duplicate key names.

Whenevaluating a JSON path expression (e.g. $.address[1 to 3]. zip) the query engine reads the JSON data in a streamingmanner. This means we do not have to load the entire data into memory first butcan start evaluating the path expression right away. It also means that if wefound what we’re looking for we can early terminate the evaluations Forinstance if we use JSON_EXISTS to check for the presence of a field called ‘id’we can return TRUE the moment we encountered one, we do not have to read therest of the JSON data. Thisimplies a couple things · Itis beneficial to place fields that are queried often to the beginning of theJSON data since early terminating will make those queries faster · Asyntax error in the JSON data may not be detected if early termination kicks insince the engine may not have to read the entire JSON data. Use ‘IS JSON’ (with acheck constraint or separately in the WHERE clause) to validate your JSON data if required. Or use ‘IS NOTJSON’ to find invalid rows and mark or remove them. If you can rely on your application to only insert syntactically correct JSON then this is not an issue for you. · Valuesfrom duplicate keys may get ignored. Thisbrings us to the issue of duplicate key names - duplicate keys inside the sameobject. Like ‘{a:true, a:false}’. Unfortunatelythe JSON specification makes no statement about those - implying that they arenot illegal. This is why IS JSON by default will not reject rows which haveduplicate keys. When I looked at otherJSON utilities (e.g. gson or http://jsonlint.com,which is great btw) I found that if JSON data with duplicate key names isprovided then all but one key are silently dropped. This is explainable if youimagine a JSON object being backed by some hashtable/map implementation. Thisimplies to me that duplicate keys are rather nasty, of no use and one shouldrather stay away from them. I invite you to correct me if you disagree! So far I have not encountered any JSON in the wild that made use of duplicate keys and I would not understand the semantics if I would find one. In orderto avoid duplicate key name we have added an option to IS JSON called ‘WITHUNIQUE KEYS’. SELECTCASE WHEN ('{a:true, a:false}' IS JSON) THEN 'valid' ELSE 'invalid' END FROMdual; -------valid whereas  SELECTCASE WHEN ('{a:true, a:false}' IS JSON WITHUNIQUE KEYS) THEN 'valid' ELSE 'invalid' END FROM dual; -------Invalid You canadd the ‘WITH UNIQUE KEYS’ option to your IS JSON check constraint but it willadd computation cost because for every object we have to keep track of the keynames we have encountered so far in order to reject a key we have seen already. Most JSON generating application and tools may nevergenerate JSON with duplicate key names so this may be a rather academicproblem. Anyways, we wanted to give you a feature to check for them but it isnot active by default because it makes IS JSON slower.

When evaluating a JSON path expression (e.g. $.address[1 to 3]. zip) the query engine reads the JSON data in a streaming manner. This means we do not have to load the entire data into memory first butca...

Storing JSON in BLOB columns

In previous postings we have shown examples of JSON datastored in a VARCHAR2 and CLOB columns. VARCHAR2values have a maximum length of is 32767 bytes whereas CLOB has no such limitation.Both are textual data types so anyselected value (if displayed in SQLPlus or SQLDeveloper) is directly readable. But there are reasons why you may want toconsider storing your JSON data as BLOBs: CLOB data is stored in UCS2 encoding which uses two bytesper character (similar to UTF16). ForJSON in most common UTF8 encoding (one byte per ASCII character) this means thatthe storage size may increase (even double if most characters are ASCII). AlsoIO would increase during queries and inserts. On the other hand, storing your JSON data in a BLOB column(Binary Large Object) does not have these disadvantages: here the UTF8 encodedrepresentation of JSON can be written and read without modifications. (Thisalso helps if the database character set is not AL32UTF8, since no characterset conversion is performed.) So how does our example look for BLOBs? The table creationis only slightly different. CREATE TABLE colorTab( id NUMBER, color BLOB); The check constraint (making sure only valid JSON data getsinserted) is the same: ALTER TABLE colorTabADD CONSTRAINT ensure_json CHECK (color IS JSON STRICT); Toinsert textual data into the binary data type BLOB we convert the text datatype to a binary data type. INSERT INTO colorTabVALUES(1, utl_raw.cast_to_raw ('{ "color": "black", "rgb": [0,0,0], "hex": "#000000"} ')); Thefunction ‘utl_raw.cast_to_raw’ performs the type casting so that the insertsucceeds if you issue the insert operation inside SQL (e.g. in SQL-Plus or SQLDeveloper). Important: you can directly insert bytes into the BLOB column from aclient like JDBC, no conversion is needed! Whathappens if you select the BLOB value? Since it is a binary data type it will bedisplayed as hex by default (something like 0A7B0A20202022636F6C6F72223A2022…).This is obviously not readable so we need to perform the inverse operation on aselect if we want to see the data as text: selectutl_raw.cast_to_varchar2(color) from colorTab; ------------------------------------------------------------------------{ "color": "black", "rgb": [0,0,0], "hex": "#000000"} Again,if you read the data from a client like JDBC you can read the BLOB bytesdirectly. Note: the functions ‘utl_raw.cast_to_raw’accepts a VARCHAR2 as input which is as we know limited to 32767 bytes.How do we insert larger data? By converting a CLOB to a BLOB: create or replace function clobToBlob( c IN CLOB ) RETURN BLOB is  b     BLOB;  warn  VARCHAR2(255);  cs_id NUMBER := NLS_CHARSET_ID('AL32UTF8');  do    NUMBER := 1; -- dest offset   so    NUMBER := 1; -- src offset   lc    NUMBER := 0; -- lang contextBEGIN   DBMS_LOB.createTemporary(b, TRUE );   DBMS_LOB.CONVERTTOBLOB(b, c, DBMS_LOB.LOBMAXSIZE, do, so, cs_id, lc, warn);   RETURN b; END clobToBlob;/ Thisinsert now looks like this INSERTINTO colorTab VALUES(1, clobToBlob ('{ "color": "black", "rgb": [0,0,0], "hex": "#000000"}'));

In previous postings we have shown examples of JSON data stored in a VARCHAR2 and CLOB columns. VARCHAR2 values have a maximum length of is 32767 bytes whereas CLOB has no such limitation.Both are...

Oracle RDBMS as a NoSQL JSON document store

Support for storing and querying JSON has recently been added to the Oracle Database, and is covered extensively by my colleague Beda Hammerschmidt in this blog. Developers can store and query JSON using Oracle SQL enhanced with the new JSON operators over traditional APIs such as OCI and JDBC. Now, there's also a sparkling new family of APIs called SODA (Simple Oracle Document Access) that are built on top of the JSON functionality, and deliver the experience of a NoSQL document-store with the Oracle RDBMS. Initially two implementations of SODA are available: SODA for REST, and SODA for Java. This blog entry will focus on how to get started with SODA for Java.With the SODA for Java API, instead of accessing tables via SQL/JSON queries over JDBC, the developer works with collections of documents, and performs CRUD (create, read, update, delete) operations on these collections. Under the covers, document collections are stored as Oracle RDBMS tables with JSON contents, and SODA transparently translates CRUD operations into SQL over JDBC. So you get the ease of use of a NoSQL document store, with all the benefits of the Oracle RDBMS, such as transactions.Let's build a simple app to show SODA for Java in action. Prerequisites:To work with SODA for Java, you must have Oracle Database 12.1.0.2, and you must install a patch. Oracle Database 12.1.0.2 is available at this URL: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html For the patch, go to Oracle Support (https://support.oracle.com). Select tab Patches & Updates. Search for the patch number 20885778, or access it directly at this URL: https://support.oracle.com/rs?type=patch&id=20885778Download the patch appropriate for your platform, and install it following the instructions in the README available with the patch. Finally, you also need to grant the SODA_APP role to the Oracle Database schema that will be used to store document collections (i.e. actual RDBMS tables that back these collections). So connect as sysdba to your instance, and, assuming your schema name is "scott", issue: grant SODA_APP to scott; To run SODA for Java, you need the following jars: ojdbc6.jar that ships with Oracle Database 12.1.0.2, available here javax.json-1.0.4.jar. This is the JSR353 implementation, download it from here orajsoda.jar. The SODA for Java jar. Download the latest here These three jars, javax.json-1.0.4.jar, orajsoda.jar, and ojdbc6.jar, must be in the classpath to compile and run SODA for Java apps.Now let's write a simple app that: Creates a new collection Inserts a few documents into the collection Retrieves the first inserted document by its auto-generated key Retrieves documents matching a query-by-example, or QBE Here's the code (read the comments in-line to understand what the code is doing): import java.sql.Connection;import java.sql.DriverManager;import oracle.soda.rdbms.OracleRDBMSClient;import oracle.soda.OracleDatabase;import oracle.soda.OracleCursor;import oracle.soda.OracleCollection;import oracle.soda.OracleDocument;import oracle.soda.OracleException;import java.util.Properties;public class testSODA { public static void main(String[] args) { // SODA works on top of a regular JDBC connection. // Set up the connection string: replace hostName, port, and serviceName // with the info for your Oracle RDBMS instance String url = "jdbc:oracle:thin:@//hostName:port/serviceName"; Properties props = new Properties();// Replace with your schemaName and password props.setProperty("user", "schemaName"); props.setProperty("password", "password"); Connection conn = null; try {// Get a JDBC connection to an Oracle instance conn = DriverManager.getConnection(url, props);// Get an OracleRDBMSClient - starting point of SODA for // Java application OracleRDBMSClient cl = new OracleRDBMSClient();// Get a database OracleDatabase db = cl.getDatabase(conn);// Create a collection with the name "MyJSONCollection". // Note: Collection names are case-sensitive. // A table with the name "MyJSONCollection" will be // created in the RDBMS to store the collection OracleCollection col = db.admin().createCollection("MyJSONCollection");// Create a few JSON documents, representing // users and the number of friends they have OracleDocument doc1 = db.createDocumentFromString( "{ \"name\" : \"Alex\", \"friends\" : \"50\" }"); OracleDocument doc2 = db.createDocumentFromString( "{ \"name\" : \"Mia\", \"friends\" : \"300\" }"); OracleDocument doc3 = db.createDocumentFromString( "{ \"name\" : \"Gloria\", \"friends\" : \"399\" }");// Insert the documents into a collection, one-by-one. // The result documents contain auto-generated // keys, among other documents components (version, etc). // Note: SODA provides the more efficient bulk insert as well OracleDocument resultDoc1 = col.insertAndGet(doc1); OracleDocument resultDoc2 = col.insertAndGet(doc2); OracleDocument resultDoc3 = col.insertAndGet(doc3);// Retrieve the first document using its auto-generated // unique ID (aka key) System.out.println ("* Retrieving the first document by its key *\n"); OracleDocument fetchedDoc = col.find().key(resultDoc1.getKey()).getOne(); System.out.println (fetchedDoc.getContentAsString());// Retrieve all documents representing users that have // 300 or more friends. Use the following query-by-example: // {friends : {$gte : 300}}. System.out.println ("\n* Retrieving documents representing users with" + " at least 300 friends *\n"); OracleDocument f = db.createDocumentFromString( "{ \"friends\" : { \"$gte\" : 300 }}"); OracleCursor c = null; try {// Get a cursor over all documents in the collection // that match our query-by-example c = col.find().filter(f).getCursor(); while (c.hasNext()) {// Get the next document fetchedDoc = c.next(); System.out.println (fetchedDoc.getContentAsString()); } } finally {// Important: you must close the cursor to release resources! if (c != null) { c.close(); } }// Drop the collection, deleting the table backing // it and collection metadata if (args.length > 0 && args[0].equals("drop")) { col.admin().drop(); System.out.println ("\n* Collection dropped *"); } } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } }} Copy and paste this code into a file called testSODA.java. Then modify the "url" String at the beginning of the programwith connection info for your Oracle RDBMS instance. Also "user" and "password" properties set at the beginning of the program need to be modified with the schema name which will contain the table backing the collection, and the password for that schema. Note that you must grant the SODA_APP role to this schema name, as described above.Compile and run testSODA.java, making sure the necessary jars are in the classpath.  For example, assuming you're in the directory where the jars are located, do:javac -classpath "orajsoda.jar" testSODA.javajava -classpath "orajsoda.jar:ojdbc6-12.1.0.2.0.jar:javax.json-1.0.4.jar:." testSODA You should see the following output: * Retrieving the first document by its key * { "name" : "Alex", "friends" : "50" } * Retrieving documents representing users with at least 300 friends * { "name" : "Mia", "friends" : "300" } { "name" : "Gloria", "friends" : "399" } This example illustrates two ways of retrieving documents from the collection: by using unique document keys, or by using QBEs. To find all userswith at least 300 friends, the following QBE was used in the code above: {"friends" : {"$gte" : 300}} As you can see, a QBE is a JSON document with a structure similar to the JSON document it's trying to match. Various operators can appear inside the QBE. In this case, $gte operator is used to find all documents where the "friends" field is set to greater than or equal to 300.To check out the table backing this collection, connect to the schema associated with your JDBC connection in the example above, using SQLPlus or another similar tool, and do: desc "MyJSONCollection"You should see: SQL> desc "MyJSONCollection" Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(255) CREATED_ON NOT NULL TIMESTAMP(6) LAST_MODIFIED NOT NULL TIMESTAMP(6) VERSION NOT NULL VARCHAR2(255) JSON_DOCUMENT BLOB As you can see a table has been created with the following columns:  ID  Stores the auto-generated key  JSON_DOCUMENT  Stores the document content  CREATED_ON  Stores the auto-generated created-on timestamp  LAST_MODIFIED  Stores the auto-generated last-modified timestamp  VERSION  Stores the auto-generated document version This table schema corresponds to the default collection configuration, but SODA collections are highly configurable. For example, the timestamp and the version columns are optional, there are many possible ways of generating the IDs or versions, etc. Custom collection configuration is covered in the documentation, and will be covered in future blog entries as well. Although most users should be fine with the defaults, custom collection configuration might be useful in some cases, such as mapping an existing table to a new collection. To drop the collection, removing the underlying table and cleaning up the metadata persisted in the database, run the example again, but this time with the "drop" argument at the end:java -classpath "orajsoda.jar:ojdbc6-12.1.0.2.0.jar:javax.json-1.0.4.jar:." testSODA dropThe output will now be different from before, since the same three documents will be inserted again. But, at the end, the collection will be dropped, and the underlying table removed.Note: do not drop the collection table from SQL.  Collections have metadata stored in the Oracle RDBMS, so must be properly dropped by using the drop() method. See the line: col.admin().drop() in the code.This concludes a very brief intro to SODA for Java, with more content to follow.More info: SODA for Java documentation: http://docs.oracle.com/cd/E63251_01/index.htmSODA for Java Javadoc: http://download.oracle.com/otndocs/java/javadocs/soda/soda6r/index.html Oracle Database as a Document Store homepage: http://www.oracle.com/technetwork/database/application-development/oracle-document-store/index.html

Support for storing and querying JSON has recently been added to the Oracle Database, and is covered extensively by my colleague Beda Hammerschmidt in this blog. Developers can store and query JSON...

Querying Facebook JSON data with JSON_TABLE

With the Java utility from the last posting we can pull tons of JSON data from the Facebook JSON API.Let's use JSON_TABLE and SQL to extract useful information!  In my example I start with just 83 rows! select count(1) from fb_tab;   COUNT(1)---------- 83 Facebook did not allow me to search further in the past and I did not want to mix postings from several sources. It seems if you want to acquire more data from one source you need to periodically pull data from the API. Anyways, every row has multiple postings, so let's see how many we have: SELECT count(1)  FROM fb_tab, JSON_TABLE(col, '$.data[*]' COLUMNS (             "Message" PATH '$.message')) "JT";     COUNT(1)----------      1325 Since users can like or comment each posting let's see how many 'reactions' these postings caused: SELECT count(1)FROM fb_tab,    JSON_TABLE(col, '$.data[*]' COLUMNS (       "Message" PATH '$.message',NESTED PATH '$.likes.data[*]' COLUMNS (         "Author_l" VARCHAR2(20) PATH '$.id'),NESTED PATH '$.comments.data[*]' COLUMNS (         "Author_c" VARCHAR2(20) PATH '$.from.id'))) "JT";   COUNT(1)----------     39910 So, our 83 rows actually contained almost 40k user interactions. I think this is a great example to illustrate the high information density of JSON. If one had normalized the data using a conventional ER model leading to multiple tables and relationships one would have ended with 40k rows - a factor of almost 500! Let's do some basic analysis using SQL: What percentage of user reactions are 'likes' versus 'comments'? SELECT (COMMENTS/TOTAL*100) || '%' "COMMENTS",        (LIKES/TOTAL*100) || '%' "LIKES" FROM (  SELECT count(1) "TOTAL",          count("Author_l") "LIKES",          count("Author_c") "COMMENTS"  FROM fb_tab,       JSON_TABLE(col, '$.data[*]' COLUMNS (          "Message" PATH '$.message',          NESTED PATH '$.likes.data[*]' COLUMNS (            "Author_l" VARCHAR2(20) PATH '$.id'),          NESTED PATH '$.comments.data[*]' COLUMNS (             "Author_c" VARCHAR2(20) PATH '$.from.id')       )) "JT"); COMMENTS LIKES---------------- -----------19.76%         80.21% You can see in this example that we make use of the 'nested path' to drill into the nested array. This was explained in a previous posting.The example uses a subquery to first do the counting. The outer query then does the calculations to come up with the percentages. The query results show us that it is obviously  much easier to get someone to click a 'like' button that to write a comment.  Next step, let's invent a little score: a like counts 1 and a comment counts 4; the score is the sum of both.Now let's find the top 10 postings based on that score: SELECT max("Message"),        (count("Author_l") +  4 * count("Author_c")) "SCORE"FROM fb_tab     JSON_TABLE(col, '$.data[*]' COLUMNS (      "Message" PATH '$.message',      o1 FOR ORDINALITY,NESTED PATH '$.likes.data[*]' COLUMNS (       "Author_l" VARCHAR2(20) PATH '$.id'),NESTED PATH '$.comments.data[*]' COLUMNS (       "Author_c" VARCHAR2(20) PATH '$.from.id'))) "JT"GROUP BY fb_tab.ROWID, jt.o1ORDER BY SCORE DESCFETCH FIRST 10 ROWS ONLY; This SQL statement does a bunch of things: Instead of counting all likes and comments we count them per posting. We use GROUP BY to express this. A group represents one posting and it is represented by the ROW ID (identifying the JSON instance; we could have chose to have another ID column in the table instead) and the position of the posting with the JSON instance (one instance/row contains up to 25 postings). We then calculate the score in the select list, order by the score and limit the result to 10 rows.  Lesson learned today: JSON_TABLE takes your JSON data and flattens it to the relational model so that you can use all of SQL to run (analytic) queries. 

With the Java utility from the last posting we can pull tons of JSON data from the Facebook JSON API. Let's use JSON_TABLE and SQL to extract useful information!  In my example I start with just 83...

Little Java helper to load Facebook postings into Oracle RDBMS

In the last two blog postings I have referred to the Facebook Graph API.  In case you want to play with the examples, but also want different data, you might find this little Java program handy: it makes https request to a Facebook page and pulls down feeds and stores them in the Oracle database. You need to obtain a current Access Token from Facebook (https://developers.facebook.com/tools/explorer). The code is something I hacked together quickly, and I probably missed a couple of things, like closing all resources properly, so use it at you own risk! I use GSON (https://code.google.com/p/google-gson/) to process the JSON result in Java (to extract the link to the next page in the pagination mechanism). import java.io.BufferedReader;import java.io.InputStreamReader;import java.net.URL;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import com.google.gson.*;import javax.net.ssl.HttpsURLConnection; public class FacebookLoader { /* Facebook info: use you own FRESH accessToken */ private static String accessToken =                  "CAACEdEose0cBAPA9dsaZAT9p6irZB4ibzAgOQLMmaNBqeo1xfclZB9Y85W3jC6ZA8ZCFoAYUypQjVJdh9mCAZAL6TrKkKMBPD5TOo7HQvB8Jf3X5ZBf13E9H5yaMTpLvfx2AJU38GIzHuMwVSvYPoKoYiOB5HKulpovjHxUWl8ZBkUIWsmLE89p3rkw6pgrZAZCGpoVfwVGZCNoRxBYi3RuINuan0nh7lJfJIZD"; private static String id = "Oracle"; // you can use other ids here private static int numPages = 100;          private URL url; /* oracle connect string info. used to build the connect string. */        private String host = "localhost"; private int port =  4539; private String service = "v16.regress.rdbms.dev.us.oracle.com";  private String password = "a"; private String user = "a"; private String connectString = "jdbc:oracle:thin:" + user + "/" + password + "@//" + host + ":" + port + "/" + service; private String tableName = "FB_TAB"; private Connection conn; public static void main(String[] args) {        // in case Java needs a proxy to call outside world. May not be needed. //System.setProperty("http.proxyHost", "yourProxyUrl"); //System.setProperty("http.proxyPort", "80"); //System.setProperty("https.proxyHost", "yourProxyUrl"); //System.setProperty("https.proxyPort", "80"); try { new FacebookLoader().load(); } catch (Exception e) { e.printStackTrace(); } System.out.println("Done"); } private void load() throws Exception { JsonParser jsonParser = new JsonParser(); String urlString = "https://graph.facebook.com/" + id + "/feed?access_token=" + accessToken; for (int i=0; i<numPages; i++){ System.out.println(i); String content = loadPage(urlString); saveJsonToDb(content); /* parse JSON to extract link to next page */ JsonObject jo = (JsonObject)jsonParser.parse(content); urlString = jo.get("paging").getAsJsonObject().get("next").getAsString(); } } private String loadPage(String urlString) throws Exception { url = new URL(urlString); HttpsURLConnection con = (HttpsURLConnection) url.openConnection(); BufferedReader br = new BufferedReader(new InputStreamReader( con.getInputStream())); String input = br.readLine(); br.close(); return input; } private void saveJsonToDb(String content) throws Exception{ System.out.println(content); DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); if (conn == null) conn = DriverManager.getConnection(connectString); String sql = "INSERT INTO " + tableName + " VALUES(?)"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, content); stmt.executeUpdate(); }}

In the last two blog postings I have referred to the Facebook Graph API.  In case you want to play with the examples, but also want different data, you might find this little Java program handy: it...

The new SQL/JSON Query operators (Part5: JSON_TABLE, Nested Path, Ordinality Column)

The examples in the previous posting showed how JSON_TABLE iteratedover a single array and returned JSON values as column values. This raises onequestion: What if arrays are nested?The sample JSON data (pulled from the Facebook Graph API) contains and multiplepostings (array) and each posting can have multiple comments or ‘likes’ (both arrays). Other data may have more levels of nesting since JSON allows arbitrary nestingof array. So how do we drill down into nested array? The answer is toadd a NESTED PATH with a new COLUMNS clause to the JSON_TABLE: SELECT jt.*   FROM fb_tab,        JSON_TABLE(col, '$.data[*]' COLUMNS (          "Message" PATH '$.message',          "Type" VARCHAR2(20) PATH '$.type',          "ShareCount" NUMBER PATH '$.shares.count' DEFAULT 0 ONERROR,           NESTED PATH '$.likes.data[*]' COLUMNS (            "Author" PATH'$.name' ))) "JT"; Message                           Type ShareCount Author--------------------------------------------------------------------------------How are Baxters Food Group and... link      0    Starup HajiHow are Baxters Food Group and... link      0    Elaine DalaIt's important to have an IT a... link      5    Asal AlibigaIt's important to have an IT a... link      5    Hasan Reni You can see that if a nested array produces rows then values are repeated for the outer columns. In the example this means that 'Message', 'Link' and 'ShareCount' are repeated for all likes of the same posting. Makes sense because the likes refer to the posting. (TheJOIN between the inner and the outer COLUMNS clause is a so called ‘OUTERJOIN’. This means that data which has no nested array (where the NESTED PATHdoes not select anything) will not be suppressed - all columns of the inner COLUMNS clause haveNULL values instead.) As youcan see the nested (inner) item is now responsible for the number of row; wesee a new row for each item in the inner array. Values for the outer COLUMNSclause are getting repeated because they are the same for all values of theinner array (i.e. all comments refer to the same posting). Sibling arrays  Now whathappens if multiple arrays are not nested but on the same level? In theFacebook example this would apply for the array of ‘likes’ and the array of‘comments’. Both arrays are nested under the posting but they’re both on thesame level. We call them ‘sibling arrays’. Semantically,sibling arrays represent different ‘things’: a like at position X has nothingto do with a comment at the same position X. This is why we return values forsibling arrays in different rows with only one sibling array at the timereturning column value. Thus the totalnumber of returned rows is the sum of the items in the sibling array and notthe (Cartesian) product. (The Join between the sibling arrays is a UNION join.) SELECT"Message", "Author_l","Author_c"FROM fb_tab,     JSON_TABLE(col, '$.data[*]' COLUMNS (       "Message" PATH '$.message', NESTED PATH '$.likes.data[*]' COLUMNS (         "Author_l"VARCHAR2(20) PATH '$.name'), NESTED PATH '$.comments.data[*]' COLUMNS (         "Author_c"VARCHAR2(20) PATH '$.from.name') )) "JT"; Message           Author_l   Author_c--------------------------------- --------------- --------------------How are Baxters Food Group and... Starup HajiHow are Baxters Food Group and... Elaine DalaHow are Baxters Food Group and... Asal AlibigaIt's important to have an IT a... Hasan ReniIt's important to have an IT a...                 Cesar Sanchez Ordinality Column Let’s assume you have several nested array. How do we keeptrack of the hierarchy when unnesting the data? Or asked differently: For twoor more column value originating from an inner array how do we now if theybelong to the same (or different) outer values? In the example above the repeating message may hint the common parent but what if there were duplicates? How could we distinguish them?  Here we need anartificial example to illustrate the point: The following two JSON instances are slightly different, they contain the same scalar values but the hierarchy is different. Look at the 'd' values in both instances:  Data instance 1:{"a":"a1",  "b": [        {"c": "c1",         "d": [1,2]}      ]} Data instance 2 {"a":"a1", "b": [        {"c": "c1",         "d": [1]},        {"c": "c1",         "d": [2]}       ]} When projecting out all scalar values one get’s this tablewith 2 rows and 3 columns for both instances SELECT jt.*FROM ord_tab,       JSON_TABLE(col, '$' COLUMNS (       "a" VARCHAR2(5) PATH '$.a',       NESTED PATH '$.b[*]' COLUMNS (          "c" VARCHAR2(5) PATH '$.c',          NESTED PATH '$.d[*]' COLUMNS (             "d" NUMBER PATH '$'          )       )     )) jt; a     c     d----- ----- ----------a1    c1     1a1    c1     2 a1    c1      1a1    c1     2  Clearly, after this relational projection we would not beable to differentiate between the two instances. What we lost is the hierarchy information:we do not know if the values for D are under the same or different ‘parents’ ,i.e enclosing object. To preserve this information we can add another column to each columns clause witha sequence number that is auto incremented for every new array item within the same parent. When the parent changes then sequence starts over again. SELECT jt.*FROM ord_tab,       JSON_TABLE(col, '$' COLUMNS (       "a" VARCHAR2(5) PATH '$.a',       NESTED PATH '$.b[*]' COLUMNS (          "c" VARCHAR2(5) PATH '$.c', "seq" FOR ORDINALITY,          NESTED PATH '$.d[*]' COLUMNS (             "d" NUMBER PATH '$'          )       )     )) jt; a     c seq d----- ----- ---------- ----------a1    c1 1 1a1    c1 1 2 a1    c1 1 1a1    c1 2 2 Now, but looking at the column 'seq' one can see that the first two values for 'd' are under the same parent. The second two values for 'd' are under separate parents.This way we can preserve the hierarchy information. One can add an ordinality column into every columns clause. We invite you to give us feedback; for instance as a comment under each posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

The examples in the previous posting showed how JSON_TABLE iterated over a single array and returned JSON values as column values. This raises one question: What if arrays are nested?The sample JSON...

The new SQL/JSON Query operators (Part4: JSON_TABLE)

After reading the previous blog postings you should know about the new SQL/JSON operators JSON_VALUE, JSON_EXISTS and JSON_QUERY. Now let’s look at the most powerful SQL/JSON addition: JSON_TABLE: JSON_TABLE is actually not an operator (or function) but something we call a ‘row source’: Given one input (JSON data ) JSON_TABLE can give us multiple outputs (rows). A common use case is to un-nest repeating values inside the JSON data. Each item of an array gets its own row, and values from that item (json object) are mapped to columns. As a consequence we can represent (nested) JSON data fully relational and leverage any relational mechanism (queries, views, reporting, relational tools) on top of JSON_TABLE. To illustrate JSON_TABLE we need a little more complex (nested) JSON data. Instead of inventing something let’s pull some real data from a common API: Facebook. I have removed a few fields in this example to keep it shorter and anonymized some values. But you can pull your own JSON data from the Facebook Graph API (https://developers.facebook.com/docs/graph-api). CREATE TABLE fb_tab (col CLOB, constraint json_con_1 CHECK (col IS JSON)); INSERT INTO fb_tab VALUES(' {  "data": [{    "from": {     "category": "Computers/technology",     "name": "Oracle"    },    "message": "How are Baxters Food Group and Elsevier taking their businesses to...",    "link": "http://onforb.es/1JOki7X",    "name": "Forbes: How The Cloud Answers Two Businesses Need For Speed ",    "description": "Cloud computing can support a companys speed and agility, ...",    "type": "link",    "created_time": "2015-05-12T16:26:12+0000",    "likes": {     "data": [{       "name": "Starup Haji"      },      {       "name": "Elaine Dala"      }     ]    }   },   {    "from": {     "category": "Computers/technology",     "name": "Oracle"    },    "message": "Its important to have an IT approach that not only red...",    "link": "http://www.forbes.com/sites/oracle/2015/05/07/3-ways-you-can-avoid-sp...",    "name": "Forbes: 3 Ways You Can Avoid Spending Too Much On IT ",    "description": "Oracles suite of SaaS applications not only reduces costs but...",    "type": "link",    "created_time": "2015-05-11T19:23:11+0000",    "shares": {     "count": 5    },    "likes": {     "data": [{       "name": "Asal Alibiga"      },      {       "name": "Hasan Reni"      }     ]    },    "comments": {     "data": [{      "from": {       "name": "Cesar Sanchez"      },      "message": "Thanks for this information",      "created_time": "2015-05-12T02:52:09+0000",      "like_count": 1     }]    }   }  ] } '); commit; This (shortened) data contains two posting on the Oracle Facebook feed. People can like and comment postings. Each posting is an item in the array that can be accessed with the path expression $.data. Since JSON _Table produces rows we need to tell it when to start a new row. This is done by providing a path expression that selects every item of the collection (array) that we want to project as a separate row. In this case we want a new row for every posting. The path expression is therefore: $.data[*]. Then for every item that is selected by this ‘row path expression’ we select the columns values by providing a relative path expression, for instance $.message. So, how does the syntax look like? JSON_TABLE is a new keyword used in the FROM clause of the query. It uses an implicit LATERAL JOIN, therefore the base table is selected first and the JSON column is the input to the JSON_TABLE: SELECT jt.* FROM   fb_tab,        JSON_TABLE(col, '$.data[*]'          COLUMNS (            "Message" PATH '$.message'        )) "JT"; Message -------------------------------------------------------------------------------- How are Baxters Food Group and Elsevier taking their businesses... It's important to have an IT approach that not only reduces c... As you can see this query extracts the value of the 'message' field and gives us one row per value. We can easily add multiple column to the COLUMN clause and customize them with an optional data type and error clause: SELECT jt.* FROM   fb_tab,        JSON_TABLE(col, '$.data[*]'           COLUMNS (             "Message"                 PATH '$.message',             "Type"       VARCHAR2(20) PATH '$.type',             "ShareCount" NUMBER       PATH '$.shares.count' DEFAULT 0 ON ERROR         )) "JT";   Message                                            Type ShareCount -------------------------------------------------------------------------------- How are Baxters Food Group and Elsevier takin... link 0 It's important to have an IT approach that not ... link 5 After this you could easily filter all posting that where shared very often by adding a simple WHERE clause: ...WHERE "ShareCount" > 20   Each column can also have the semantics of JSON_VALUE, JSON_QUERY or JSON_EXISTS. The default is JSON_VALUE. The next example shows you all three semantics: SELECT jt.* FROM fb_tab,      JSON_TABLE(col, '$.data[*]'       COLUMNS (        "Message" PATH '$.message',        "Type" VARCHAR2(20)PATH '$.type',        "ShareCount" NUMBER  PATH '$.shares.count' DEFAULT 0 ON ERROR,        "HasComments" NUMBER EXISTS  PATH '$.comments',        "Comments" VARCHAR2(4000) FORMAT JSON PATH '$.comments'     )) "JT"; Message                            Type ShareCount  HasComments  Comments -------------------------------------------------------------------------------- How are Baxters Food Group and... link....0............0  It's important to have an IT a... link....5............1.........{"data":[{"i... JSON_EXISTS semantics uses the keyword EXISTS.  JSON_QUERY semantics uses the keywords FORMAT JSON. All options of the operators (e.g. array wrapper options for JSON_QUERY) can be used on a column of JSON_TABLE, too.    So far today, it is late. In the next blog I will explain how we can drill into the nested array. 

After reading the previous blog postings you should know about the new SQL/JSON operators JSON_VALUE, JSON_EXISTS and JSON_QUERY. Now let’s look at the most powerful SQL/JSON addition: JSON_TABLE: JSON_...

The new SQL/JSON Query operators (Part3: JSON_EXISTS)

Thisentry is about JSON_EXISTS: JSON_EXISTS takes a path expression (potentiallywith a predicate) and checks if such path selects one (or multiple) values inthe JSON data. That’s it basically, let’s show some example and use cases where you may want to use it. First,let’s create a table and insert some sample data: CREATE TABLEcustomerTab (custData VARCHAR2(4000),CONSTRAINT ensure_json2 CHECK (custData ISJSON STRICT)); INSERT INTOcustomerTab VALUES ('{"id":1, "name" : "Jeff"}'); INSERT INTOcustomerTab VALUES ('{"id":2, "name" : "Jane","status":"Gold"}'); INSERT INTOcustomerTab VALUES ('{"id":3, "name" : "Jill","status":["Important","Gold"]}'); INSERT INTOcustomerTab VALUES ('{"name" : "John","status":"Silver"}'); commit; Let’sfind rows where a field named ‘status’ exists: SELECTcount(1)FROMcustomerTabWHEREJSON_EXISTS (custData, '$.status'); ---------- 3 In the previous example only the existence of a field called 'status' mattered. What value or type it has is irrelevant.Now, let’sfind ‘gold’ customers, i.e. customers with a status field which have a corresponding value 'gold': SELECTcount(1)FROMcustomerTabWHEREJSON_EXISTS (custData, '$.status?(@ == "Gold")'); ---------- 2 The lastquery contains a predicate, this is expressed by using a question mark followedby a Boolean condition in parentheses. The symbol’@’ denotes the currentcontext, i.e. the key/value pair selected by the path expression before thepredicate. We will show further examples with predicted in future blog entries. You mayask at this point why you cannot use JSON_VALUE or JSON_QUERY in the WHERE clause for thispurpose? There are a couple of reasons: JSON_EXISTS checks for the existence of a value. Since JSON can have a ‘null’ value one could not differentiate a JSON ‘null’ from a missing value in JSON_VALUE. JSON_VALUE can only select and return scalar values. As you can see for row 3 (Jill) the status “Gold” occurs inside an array. Here for the evaluation of the predicate only the existence of “Gold” inside the array matters, not the position. Since JSON_VALUE can only return one (scalar SQL) value we would have to test every array position which is not practical. Similarly, JSON_QUERY is not suitable to compare the selected value(s) with one scalar value.  At thispoint we can use JSON_EXISTS to enforce that every customer has an ‘id’ fieldusing a CHECK constraint. DELETE FROMcustomerTabWHERE NOT (JSON_EXISTS(custData, '$.id')); 1 row deleted. ALTER TABLEcustomerTab ADD CONSTRAINT ensure_id CHECK (JSON_EXISTS(custData, '$.id')); Withthis check constraint the row for ‘John’ cannot be inserted although the datais syntactically correct JSON. Since the JSON data has no ‘id’ field it will berejected. JSON_EXISTS allows TRUE ON ERROR and FALSE ON ERROR as error handlers.  I invite you to provide feedback; for instance as a comment under this posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

This entry is about JSON_EXISTS: JSON_EXISTS takes a path expression (potentially with a predicate) and checks if such path selects one (or multiple) values in the JSON data. That’s it basically, let’s...

The new SQL/JSON Query operators (Part2: JSON_QUERY)

Next in our introduction to the new SQL/JSON operators isJSON_QUERY. In the previous two posts we have shown JSON_VALUE. So why do we need another operator? JSON_QUERY is complementary to JSON_VALUE. JSON_VALUE takes JSON a input and returns onescalar SQL value. Think of JSON_VALUE as the ‘bridge’ from JSON to SQL. (We willuse it later for relational concepts that operate on SQL values like virtual columns, functionalindexing, etc).JSON_QUERY on the other hand always returns JSON, i.e. an object or an array. Thisimplies that JSON_QUERY could be chained (JSON in – JSON out) versus the outputof JSON_VALUE can never be used as the input for another operator that expect aJSON input. Okay, sounds all very complex but it is not. Let’s startwith an example and create a table with test data. drop tablecustomerTab; CREATE TABLEcustomerTab (custData VARCHAR2(4000),CONSTRAINT ensure_json2 CHECK (custData ISJSON STRICT)); INSERT INTOcustomerTab VALUES ('{ "custNo":2, "name" : "Jane", "status":"Gold", "address": { "Street": "Main Rd1", "City": "San Jose", "zip": 95002}}'); INSERT INTOcustomerTabVALUES ('{ "custNo":3, "name" : "Jill", "status":["Important","Gold"], "address": { "Street": "Broadway2", "City": "Belmont", "zip": 94065}}'); commit; As one can see every customer has an address which is a JSONobject. We can return this embedded object using JSON_QUERY. SELECTJSON_QUERY(custData, '$.address')FROMcustomerTab; -----------------------------------------------------{"Street":"MainRd 1","City":"San Jose","zip":95002}{"Street":"Broadway2","City":"Belmont","zip":94065} Similarly,we can select an inner array SELECTJSON_QUERY(custData, '$.status')FROM customerTab; ----------------------------------------------["Important","Gold"] But,what happened here? We have selected the field called ‘status’ but only onevalue shows up, the other value is NULL! Maybe this NULL comes from a maskederror (remember, the default error handler is to return NULL). So, let’s makesure any error gets reported. We run the same query with ERROR ON ERROR insteadof default NULL ON ERROR. SELECTJSON_QUERY(custData, '$.status' ERROR ON ERROR)FROM customerTab; ORA-40480: resultcannot be returned without array wrapper Now,what does that mean? Basically, the path expression ($.status) has selectedsomething but it cannot be returned because it is not a JSON value (not anobject or array). But we give you a hint: wrap it with an array and it can bereturned. Let’s do it then: SELECTJSON_QUERY(custData, '$.status' WITH ARRAY WRAPPER)FROMcustomerTab; -----------------------------------------------------------["Gold"][["Important","Gold"]] Okay,now every selected value gets wrapped inside an array and the values can bereturned (the array wrapper turned it into JSON). Also, we can get back theselected value by unwrapping it (in a client application or in JavaScript inthe web browser). But sometimes we maywant to only wrap a value if needed and not wrap a value which is already JSON.Can we do that? Yes, by adding they keyword ‘CONDITIONAL’: SELECT JSON_QUERY(custData, '$.status' WITH CONDITIONAL ARRAY WRAPPER)FROMcustomerTab; --------------------------------------------------------------------------------["Gold"]["Important","Gold"] Here wego: the scalar value “Gold” needed wrapping, the other row’s value was alreadyJSON, it did not get another array wrapper. ButJSON_QUERY can do more: Let’sinsert a customer with multiple addresses: INSERT INTOcustomerTabVALUES ('{ "custNo":3, "name" : "Jim", "status": "Silver", "address":[ { "Street": "Fastlane4", "City": "Atherton", "zip": 94027 }, { "Street": "Slowlane5", "City": "SanFrancisco", "zip": 94105 } ]}'); commit; Obviously,since both addresses inside one array, we can select the array as a whole. Similarly we canselect one address out of the array because each address itself is a JSONobject. But how can we select the city names? We use a wildcard (*) to accessall members of the array: SELECTJSON_QUERY(custData, '$.address[*].City' WITH ARRAY WRAPPER) FROMcustomerTab; ----------------------------------------------------------------["SanJose"]["Belmont"]["Atherton","SanFrancisco"] This iswhere our JavaScript inspired path language exceeds the capabilities ofJavaScript navigation. Using the wildcard one can select more than one memberof the array. ‘*’ selects all. But we can also select a subset like [1,2,3] or[1 to 5] or even [1,2, 5 to 10]. We invite you to provide feedback; for instance as a comment under this posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

Next in our introduction to the new SQL/JSON operators isJSON_QUERY. In the previous two posts we have shown JSON_VALUE. So why do we need another operator? JSON_QUERY is complementary to JSON_VALUE. JS...

The new SQL/JSON Query operators (Part1: JSON_VALUE)

The simplified syntax shown in the last blog allows easy but limited functionality to work with JSON data. This is why Oracle has added more powerful SQL operators to work with JSON data. Starting with database release 12.1.0.2 Oracle provides these new SQL operators:   JSON_VALUE:  to select one scalar value in the JSON data and return it to SQL. (JSON_VALUE is the ‘bridge’ from a JSON value to a SQL value). JSON_EXISTS: a Boolean operator typically used in the WHERE clause to filter rows based on properties in the JSON data. JSON_QUERY: an operator to select (scalar or complex) value in the JSON data. In contrast to JSON_VALUE which always returns one scalar value, JSON_QUERY returns a JSON value (object or array). With JSON_QUERY a user can also select multiple values and have them wrapped inside a JSON array. JSON_TABLE: the most powerful operator that exposes JSON data as a relational view. With JSON_TABLE you can turn your JSON data into a relational representation. All operators have in common that they accept one (or more) path expressions to select values or fragments in the JSON data.The operators have an ‘error handler’ which specified what to do in case an error is encountered, and options how selected values are being returned.(You may see more relational vendors supporting these operators in the future since it's a standard proposal: goo.gl/SLT4SQ) Before we look at the operators let’s create a table and insert some sample data. DROP TABLE colorTab; CREATE TABLE colorTab (color VARCHAR2(32767), CONSTRAINT ensure_json CHECK (color IS JSON STRICT)); INSERT INTO colorTab VALUES('{"name": "black","rgb": [0,0,0],"hex": "#000000"}'); INSERT INTO colorTab VALUES('{"name": "orange red","rgb": [255,69,0],"hex": "#FF4500"}'); INSERT INTO colorTab VALUES('{"name": "dark orange","rgb": [255,140,0],"hex": "#FF8C00"}'); commit; Now, let’s look at the new operator  JSON_VALUE.I will explain error handlers and other concepts in a little more detail, this makes this posting a little longer. But since these concepts also apply for the other operators it makes sense to present them properly. SELECT JSON_VALUE(color, '$.name') from colorTab;---------------------------------------------------------black orange reddark orange This query expression is probably the simplest version of a JSON_VALUE expression. The first argument to the JSON_VALUE operator is the input (column name) which provides the JSON data – in this case it is the column named ‘color’. The second parameter is a path expression. A path expression always starts with ‘$’. ('$' alone refers to the input data, later I will show how you can pass variables and refer to them using '$var'...).After this you use dots and square brackets (similar to JavaScript) to navigate inside to the desired key/value pair. As a regular SQL operator JSON_VALUE can also be used outside the SELECT list, for instance in the WHERE clause or ORDER BY clause and combined with other SQL operators. Let’s illustrate this in the next example: SELECT   JSON_VALUE(color, '$.hex'),  JSON_VALUE(color, '$.name') from colorTabWHERE UPPER(JSON_VALUE(color, '$.name')) like '%ORANGE%'ORDER BY JSON_VALUE(color, '$.name'); ---------------------------------------------------------#FF8C00 dark orange#FF4500 orange red The default output of JSON_VALUE is a VARCHAR2(4000). Since names of colors are typical much shorter than 4000 characters lets make the output shorter. For this we use the (optional)RETURNING clause: SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10)) from colorTab; -------------------------------------blackorange red What happens now? We only see two colors (‘black’ and ‘orange red’) . Instead of ‘dark orange’ we see a NULL. Why is that? The color's name (dark orange) does not fit into the 10 character wide output we specified. We encountered an error but the default error handler suppressed it and gave us a NULL instead. At this point we have four options: Bump the output to a larger value, e.g VARCHAR2(20) Truncate the output , this gives us a partial value instead of NULL SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) TRUNCATE) FROM colorTab; ----------blackorange reddark orang        (e is missing)  Tell the error handler to not suppress the error but raise it instead SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) ERROR ON ERROR) FROM colorTab; ERROR:ORA-40478: output value too large (maximum: 10) Tell the error handler to return a default value instead of NULL SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) DEFAULT '#BADCOLOR#' ON ERROR) FROM colorTab;  ----------blackorange red#BADCOLOR# At this point you may understand why we call the simplified syntax ‘simplified’ ;-) But keep in mind that all these options have valid use cases and you may appreciate that they exist! The RETURNING clause also allows us to return non-textual values, for instance a number: SELECT JSON_VALUE(color, '$.rgb[0]' RETURNING NUMBER) "RED",JSON_VALUE(color, '$.rgb[1]' RETURNING NUMBER) "GREEN",JSON_VALUE(color, '$.rgb[2]' RETURNING NUMBER) "BLUE"FROM colorTab;        RED GREEN    BLUE---------- ---------- ----------         0    0       0       255   69       0       255  140       0 Please note that this example also shows how an array access is specified in a path expression using the square brackets [], like in JavaScript. Other return data types you can chose from include RAW, DATE and TIMESTAMP (there is no format clause for Date and Timestamp!!, the date has to follow ISO 8601 format(http://en.wikipedia.org/wiki/ISO_8601)). SELECT JSON_VALUE ('{"created" : "2015-04-29T15:16:55"}', '$.created' RETURNING TIMESTAMP)FROM DUAL; ---------------------------------------------------------------------------29-APR-15 03.16.55.000000 PM The last output might look differently for you if you NLS settings are different than mine.  Dates, Timestamps  You may ask now why there is there no format clause? The reason is that JSON has no syntax to represent a date. So in JSON a date would be represented as a string. If different formats were used the JSON date value would not be self-contained anymore, one would need additional information to interpret it. We believe that this has the potential of creating headaches down the road wherepeople misinterpret dates (e.g. “2-1-2015” is February, 1st in the US but 2nd January in Germany). Therefore we strongly recommend to only use one format (ISO 8601). Booleans The opposite situation exists for Boolean values: JSON supports those. Oracle not (at least not in SQL, PL/SQL is different). We gave you two choices: as string you see the values ‘true’| ‘false’ or when the RETURN CLAUSE specifies a number we return 1 | 0. SELECT JSON_VALUE('{"a":true}', '$.a') FROM DUAL;---------------------------------------------- true SELECT JSON_VALUE('{"a":true}', '$.a' RETURNING NUMBER) FROM DUAL;---------------------------------------------- 1 We invite you to provide feedback; for instance as a comment under this posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

The simplified syntax shown in the last blog allows easy but limited functionality to work with JSON data. This is why Oracle has added more powerful SQL operators to work with JSON data. Starting...

Simple Queries

In the previous blogs we learned how to store JSON data inOracle. Now let’s query it. First let’s run these statements to  create a table andinserts two rows with JSON data: DROP TABLE colorTab; CREATE TABLE colorTab ( id NUMBER, color VARCHAR2(32767), CONSTRAINT ensure_json CHECK (color ISJSON STRICT)); INSERT INTO colorTab VALUES(1, '{"name":"black","rgb":[0,0,0],"hex":"#000000"}'); INSERT INTO colorTab VALUES(2, '{"name":"orange red","rgb":[255,69,0],"hex":"#FF4500"}'); commit; Simplified Syntax The so called ‘simplified syntax’ allows to select keysinside the JSON data and to return their corresponding value. You select a keyby navigating to it using a ‘path expression’. Sounds complex but it’s notreally: you uses a dot (‘.’) to navigate inside an object and square brackets(‘[]’) to navigate inside an array. Sounds familiar? It’s pretty much the sameas you would write in JavaScript. So let’s go: SELECT c.color.namefrom colorTab c; NAME--------------------------------------------------------------------------------blackorange red returns us two rows with the values ‘black’ and ‘orange red’. Please note that in the SELECT clause ‘c’ is the tablealias, ‘color’ is the column name and ‘name’ is a field inside the JSON data. SELECTc.color.rgb[0] "RED",c.color.rgb[1] "GREEN", c.color.rgb[2] "BLUE" FROM colorTab c; This returns three columns: the values of the three colorcomponents Red, Green and Blue. RED   GREEN      BLUE ---------- ---------- ---------- 0   0      0 255   69      0 So what happens if we use a key name that does not exist inthe data, let’s say ‘description’? SELECT c.color.description from colorTab c; We get back a NULL value. No error message. This is becausein many JSON use cases there are optional fields which do not exist in everyinstance . It would be inconvenient if such flexible schema would force us to writedifferent queries. This is why we return NULL for such a case (you will learnlater how such an error can be reported). The simplified syntax also allows us to select a complexvalue (i.e. an inner array or object). select c.color.rgb from colorTab c; will return the whole array as a value for each row. RGB -------------------------------------------------------------------------------- [0,0,0] [255,69,0] The simplified syntax works very well for simple queries like the one illustrated above but it comes with some limitations: The output is alwaysa VARCHAR2(4000). Any error will be masked by NULL. And it requires thepresence of the check constraint that marks a column as a JSON column. For moregeneric JSON processing we have added a set of new operators that I will describe next. We invite you to give us feedback; for instance as a comment under each posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

In the previous blogs we learned how to store JSON data in Oracle. Now let’s query it. First let’s run these statements to  create a table and inserts two rows with JSON data: DROP TABLE colorTab; CREATE...

Storing JSON in the Oracle database

In this posting  you learn how to: Store JSON data in the Oracledatabase Pick a good data type for your JSONdata Identify rows that are JSON (or notJSON) Ensure that only JSON data can beinserted into a table Find all columns that store JSONdata Prerequisites: You need the Oracle Database Release 12.1.0.2 or later Ensure that the COMPATIBLE init.ora parameter is set to 12.1.0.2 or later (you may see an error message otherwise) To learn what JSON is please visit [http://json.org/ or http://en.wikipedia.org/wiki/JSON] We have chosen to store JSON data in existing data types instead of adding a new type. This decision allows you to choose the best data type for you use case and use existing clients. Also you get cross functional completeness right away (import export, partitioning, replication, etc). JSON can be stored as VARCHAR2,CLOB or BLOB.  The supported data types have different properties: Varchar2has a maximum length of 32k which is probably sufficient for many usecases. Clob and Blob have no lengthlimitation. Internally, Clob encodes characters as UCS2 (similar to UTF16)which means every character takes up two bytes. Blob does not perform suchre-encoding but instead stores the Unicode bytes unmodified. For this posting we use Varchar2 as data type. In our example we store a list of color names and their codes. CREATE TABLE colorTab( id NUMBER, color VARCHAR2(32767)); (In order to allow the 32k length for VARCHAR2 the init.oraparameter MAX_STRING_SIZE has to be set to EXTENDED. See [https://docs.oracle.com/database/121/REFRN/refrn10321.htm#REFRN10321]). Or replace 32767 with 4000. Let's insert a few rows: INSERT INTO colorTabVALUES(1, '{ "color": "black", "rgb": [0,0,0], "hex": "#000000"}'); INSERT INTO colorTabVALUES(2, '{ "color": "orange red", "rgb": [255,69,0], "hex": "#FF4500"}'); INSERT INTO colorTabVALUES(3, '{ color: "gold", "rgb": [255,215,0], "hex": "#FFD700 "}'); INSERT INTO colorTabVALUES(4, 'I am not valid JSON'); COMMIT; As one can see the last insert statement inserts data thatis not JSON. How can we identify such a rows? And how can we prevent invalid JSON from being insertedin the first place? This brings us to the first new JSON operator: ‘IS JSON’. Itallows us to filter column values. SELECT id, colorFROM colorTabWHERE color IS JSON; (Insteadof ISJSON one can use IS NOT JSON to find all rows which are not JSON.) If youlook carefully at the third row (gold) you’ll see that the field name ‘color’is not inside quotes. Technically, this makes the data invalid JSON because key names need to be enquoted but not everyone follows that rule. The IS JSON operator still accepts this row as JSON because our JSON parser by default runs in a tolerant mode. But not every JSON tool accepts unquoted names (try out http://jsonlint.com/). So for JSON you're sending to others you may want to be extra polite and make sure it complies to all rules. You can do this by running IS JSON in a strict mode: SELECT id, colorFROM colorTabWHERE color IS JSON STRICT; The JSONspecification (unfortunately) says nothing about duplicate key names within thesame object. For instance this is technically valid JSON altough it has two keys with the name name. { "color": "black", "rgb": [0,0,0], "hex": "#000000", "color": "white"} Again, try this out in  http://jsonlint.com/, In ourexperience duplicate key names occur vary rarely but if they do they are big troublemakers. So how can we avoid them? The clause ‘WITH UNIQUE KEYS’ checks that keys are notrepeated within the same object level (keys with the same name can occur atmultiple places in the data but not in the same object). SELECT id, colorFROM colorTabWHERE color IS JSON STRICT WITH UNIQUE KEYS; (Checkingfor unique keys comes at a price. It slows down IS JSON because nameshave to me memorized and checked for duplicates. So use it carefully!) So nowthat we understand IS JSON how can we use it to prevent non-JSON data to beinserted into our table in the first place? The answer is a check constraint. Letsadd a constraint to our table: TRUNCATE TABLEcolorTab; ALTER TABLE colorTabADD CONSTRAINT ensure_json CHECK (color IS JSON STRICT); Now,when inserting any non-JSON row will be rejected with ORA-02290: check constraint (SYS.ENSURE_JSON) violated Also,since we have added a IS JSON check constraint the table/column will show up ina new dictionary view: SQL> SELECT *FROM USER_JSON_COLUMNS; TABLE_NAME COLUMN_NAME FORMAT DATA_TYPE ---------- ---------- ---------- ---------- COLORTAB COLOR TEXT VARCHAR2 The view's are named USER_JSON_COLUMNS, DBA_JSON_COLUMNS and ALL_JSON_COLUMNS. The rely on the presence of a IS JSON check constraint.  We invite you to give us feedback; for instance as a comment under each posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

In this posting  you learn how to: Store JSON data in the Oracle database Pick a good data type for your JSON data Identify rows that are JSON (or not JSON) Ensure that only JSON data can beinserted into...

Hello World

Welcome to this new blog about exciting new features of theOracle database around JSON, REST, Document Store APIs and other related technologies.This blog is written by Oracle database developers – the people that write (anddebug) the code that runs when you issue a SQL command. The target audience of this blog are developers (we aim to providedetailed information with examples), as well as DBAs and architects, who may bemore interested in higher level information and best practices. We invite you to provide feedback; for instance as a comment under this postingor in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

Welcome to this new blog about exciting new features of the Oracle database around JSON, REST, Document Store APIs and other related technologies.This blog is written by Oracle database developers –...

Oracle

Integrated Cloud Applications & Platform Services