Db2 11 JSON Features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"top\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Db2 11 JSON Features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "There are a number of routines are that are built-in to Db2 that are used to manipulate JSON\n",
    "documents. These routines are not externalized in the documentation because they were originally used by the \n",
    "internal API's of Db2 for managing the MongoDB interface. These routines have now been made available for use in both Db2 10.5 and 11.1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "\n",
    "* [Db2 JSON Functions and Registration](#json)\n",
    "* [Path Requirements](#path)\n",
    "<p>\n",
    "* [Programming with Db2 JSON](#programming)\n",
    "  * [Inserting JSON Document (JSON2BSON)](#json2bson)\n",
    "  * [Retrieving a JSON Document (BSON2JSON)](#bson2json)\n",
    "  * [Checking a JSON Document for Consistency](#bsonvalidate)\n",
    "<p>\n",
    "* [Manipulating JSON Documents](#manipulating)\n",
    "  * [Retrieving Data](#retrieving)\n",
    "  * [Retrieving Individual Fields](#atomic)\n",
    "  * [Accessing Elements in an Array](#array)\n",
    "  * [Accessing Portions of a Structure](#structures)\n",
    "  * [Determining if a Field is Null](#nulls)\n",
    "  * [How to Join JSON Fields](#joins)\n",
    "<p>    \n",
    "* [JSON Data Types](#datatypes)\n",
    "  * [INTEGERS and BIGINT](#integers)\n",
    "  * [NUMBERS and FLOATING POINT](#numbers)\n",
    "  * [BOOLEAN Values](#boolean)\n",
    "  * [DATE, TIME, and TIMESTAMPS](#date)\n",
    "  * [Strings](#strings)\n",
    "<p> \n",
    "* [Dealing with Arrays](#arrays)\n",
    "  * [Retrieving Array Values as a Table](#table)\n",
    "  * [Determining the Size of an Array](#length)\n",
    "  * [Searching an Array for a Value](#getpos)\n",
    "<p> \n",
    "* [Updating JSON Documents](#updating)\n",
    "* [Indexing Fields in JSON Documents](#indexing)\n",
    "* [How to Simplify JSON Inserts and Retrieval](#simple)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='json'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Db2 JSON Functions\n",
    "\n",
    "There is one built-in Db2 JSON function and a number of other functions that must be registered within\n",
    "Db2 before they can be used. The names of the functions and their purpose are described below.\n",
    "  \n",
    "  - **JSON_VAL** - Extracts data from a JSON document into SQL data types\n",
    "  - **JSON_TABLE** - Returns a table of values for a document that has array types in it\n",
    "  - **JSON_TYPE** - Returns documents that have a field with a specific data type (like array, or date)\n",
    "  - **JSON_LEN** - Returns the count of elements in an array type inside a document\n",
    "  - **BSON2JSON** - Convert BSON formatted document into JSON strings\n",
    "  - **JSON2BSON** - Convert JSON strings into a BSON document format\n",
    "  - **JSON_GET_POS_ARR_INDEX** - Retrieve the index of a value within an array type in a document\n",
    "  - **JSON_UPDATE** - Update a particular field or document using set syntax\n",
    "  - **BSON_VALIDATE** - Checks to make sure that a BSON field in a BLOB object is in a correct format\n",
    "  \n",
    "Aside from the JSON_VAL function, all other functions in this list must be catalogued before first \n",
    "being used. The next set of SQL will catalog all of these functions for you. Note: These functions have already\n",
    "been catalogued."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "CREATE FUNCTION SYSTOOLS.JSON_TABLE(\n",
    "  INJSON BLOB(16M), INELEM VARCHAR(2048), RETTYPE VARCHAR(100)) \n",
    "  RETURNS TABLE(TYPE INTEGER, VALUE VARCHAR(2048))\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  NO EXTERNAL ACTION\n",
    "  DISALLOW PARALLEL\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonTable';\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.JSON_TYPE(\n",
    "  INJSON BLOB(16M), INELEM VARCHAR(2048), MAXLENGTH INTEGER) \n",
    "  RETURNS INTEGER\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  RETURNS NULL ON NULL INPUT\n",
    "  NO EXTERNAL ACTION\n",
    "  EXTERNAL NAME 'db2json!jsonType';\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.JSON_LEN(\n",
    "  INJSON BLOB(16M), INELEM VARCHAR(2048)) \n",
    "  RETURNS INTEGER\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  NO EXTERNAL ACTION\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonLen';\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.BSON2JSON(INBSON BLOB(16M)) RETURNS CLOB(16M)\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  NO EXTERNAL ACTION\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonBsonToJson';\n",
    "\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.JSON2BSON(INJSON CLOB(16M)) RETURNS BLOB(16M)\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE \n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  NO EXTERNAL ACTION\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonToBson';\n",
    "  \n",
    "CREATE FUNCTION SYSTOOLS.JSON_GET_POS_ARR_INDEX(\n",
    "  INJSON BLOB(16M), QUERY VARCHAR(32672) FOR BIT DATA) \n",
    "  RETURNS INTEGER\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  CALLED ON NULL INPUT\n",
    "  NO EXTERNAL ACTION\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonGetPosArrIndex';\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.JSON_UPDATE(\n",
    "  INJSON BLOB(16M), INELEM VARCHAR(32672)) \n",
    "  RETURNS BLOB(16M)\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  CALLED ON NULL INPUT\n",
    "  NO EXTERNAL ACTION\n",
    "  SCRATCHPAD 2048\n",
    "  EXTERNAL NAME 'db2json!jsonUpdate2';\n",
    "\n",
    "CREATE FUNCTION SYSTOOLS.BSON_VALIDATE(\n",
    "  INJSON BLOB(16M)) \n",
    "  RETURNS INT\n",
    "  LANGUAGE C\n",
    "  PARAMETER STYLE SQL\n",
    "  PARAMETER CCSID UNICODE\n",
    "  NO SQL\n",
    "  NOT FENCED\n",
    "  DETERMINISTIC\n",
    "  ALLOW PARALLEL\n",
    "  RETURNS NULL ON NULL INPUT\n",
    "  NO EXTERNAL ACTION\n",
    "  EXTERNAL NAME 'db2json!jsonValidate';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='path'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Path Statement Requirements\n",
    "\n",
    "All of the Db2 JSON functions have been placed into the SYSTOOLS schema. This means that in order to execute\n",
    "any of these commands, you must prefix the command with SYSTOOLS, as in SYSTOOLS.JSON2BSON. In order to \n",
    "remove this requirement, you must update the CURRENT PATH value to include SYSTOOLS as part of it. The \n",
    "SQL below will tell you what the current PATH is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql VALUES CURRENT PATH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If SYSTOOLS is not part of the path, you can update it with the following SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SET CURRENT PATH = CURRENT PATH, SYSTOOLS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From this point on you won't need to added the SYSTOOLS schema on the front of any of your SQL \n",
    "statements that refer to these Db2 JSON functions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='programming'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Programming with the JSON SQL Functions\n",
    "\n",
    "The functions that are listed below give you the ability to retrieve and manipulate JSON documents that\n",
    "you store in a column within a table. What these functions do not do is let you explore the structure of a\n",
    "JSON document. The assumption is that you are storing \"known\" JSON documents within the column and that\n",
    "you have some knowledge of the underlying structure.\n",
    "\n",
    "What this means is that none of the functions listed below will let you determine the fields that are\n",
    "found within the document. You must already know what these fields and their structure (i.e. is it an array)\n",
    "are and that you are either trying to extract some of these fields, or need to modify a field within the document. \n",
    "If you need to determine the structure of the JSON document, you are better off using the JAVA APIs that are available for \n",
    "manipulating these types of documents.\n",
    "\n",
    "To store and retrieve an entire document from a column in a table, you would use:\n",
    "\n",
    "- **BSON2JSON** - Convert BSON formatted document into JSON strings\n",
    "- **JSON2BSON** - Convert JSON strings into a BSON document format\n",
    "\n",
    "You can also verify the contents of a document that is stored in a column by using the BSON_VALIDATE function:\n",
    "\n",
    "- **BSON_VALIDATE** - Checks to make sure that a BSON field in a BLOB object is in a correct format\n",
    "\n",
    "BSON is the binary format use to store JSON documents in MongoDB and is also used by Db2. Documents are always stored\n",
    "in BLOBS (binary objects) which can be as large as 16M. BLOBs can be defined to be INLINE, which will result in \n",
    "improved performance for any of these JSON functions. If you create a table with a BLOB column, try to use a size that\n",
    "will fit within a Db2 page size. For instance, if you have a 32K page size for your data base, creating BLOB objects less \n",
    "than 32000 bytes in size will result in better performance:\n",
    "\n",
    "<pre>\n",
    " CREATE TABLE JSON_EMP (\n",
    "   EMP_INFO BLOB(4000) INLINE LENGTH 4000\n",
    " );\n",
    "</pre>\n",
    "\n",
    "If a large object is not inlined, or greater than 32K in size, the resulting object will be placed into a \n",
    "large table space. The end result is that BLOB objects will not be kept in bufferpools (which means a direct read is \n",
    "required from disk for access to any BLOB object) and that two I/Os are required to get any document. One I/O is required to get the base page, while the second is needed to get the BLOB object. By using the INLINE option and keeping the BLOB size below the page size, we can avoid both of these performance overheads."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Tables that support JSON Documents\n",
    "\n",
    "To create a table that will store JSON data, you need to define the column so that is of \n",
    "a binary type. The JSON field must be created as a BLOB (at the time of writing, the use \n",
    "of the VARBINARY data type has not been verified) column. In order to ensure\n",
    "good performance, you should have BLOB specified as INLINE if possible. \n",
    "\n",
    "Of course, if your JSON object is greater than 32K, there is no way it will be able to sit on a \n",
    "Db2 page, so you will need to use the large object format. However, if the object is significantly smaller than\n",
    "32K, you will end up getting better performance if it can remain on one Db2 page."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql -q DROP TABLE TESTJSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE TABLE TESTJSON\n",
    "  (\n",
    "  JSON_FIELD BLOB(4000) INLINE LENGTH 4000\n",
    "  )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='json2bson'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON2BSON: Inserting a JSON Document  \n",
    "\n",
    "Inserting into a column requires the use of the JSON2BSON function. The JSON2BSON function (and BSON2JSON) are used to transfer data in and out of a traditional Db2 BLOB column. There is no native JSON data type in Db2. Input to the JSON2BSON function must be a properly formatted JSON document. In the event that the document does not follow proper JSON rules, you will get an error code from the function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO TESTJSON VALUES ( JSON2BSON('{Name:\"George\"}') )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is an example of a poorly formatted JSON document."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -j select bson2json(json_field) from testjson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO TESTJSON VALUES\n",
    "( JSON2BSON('{Name:, Age: 32}'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='bson2json'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BSON2JSON: Retrieving a JSON Document\n",
    "\n",
    "Note that the data that is stored in a JSON column is in a special binary format called BSON. Selecting from the field will only result in hexadecimal characters being displayed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT CAST(JSON_FIELD AS VARCHAR(60)) FROM TESTJSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to extract the entire contents of a JSON field, you need to use the BSON2JSON function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -j\n",
    "SELECT BSON2JSON(JSON_FIELD) FROM TESTJSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One thing that you should note is that the JSON that is retrieved has been modified slightly so that\n",
    "all of the values have quotes around them to avoid any ambiguity. Note that we didn't necessarily require them\n",
    "when we input the data. For instance, our original JSON document what was inserted looked like this:\n",
    "\n",
    "<pre>\n",
    "{Name:\"George\"}\n",
    "</pre>\n",
    "\n",
    "What gets returned is slightly different, but still considered to be the same JSON document. You must ensure that the\n",
    "naming of any fields is consistent between documents. \"Name\", \"name\", and \"NAME\" are all considered different fields. One option is to use lowercase field names, or to use camel-case (first letter is capitalized) in all of your field definitions. The important thing is to keep the naming consistent so you can find the fields in the document.\n",
    "\n",
    "<pre>\n",
    "{\"Name\":\"George\"}\n",
    "</pre>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='bsonvalidate'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BSON_VALIDATE: Checking the format of a JSON document\n",
    "\n",
    " \n",
    "There is no validation done against the contents of a BLOB column which contains JSON data. \n",
    "As long as the JSON object is under program control and you are using the JSON functions,\n",
    "you are probably not going to run across problems with the data. You should probably stick to either Db2 JSON\n",
    "functions to access your JSON columns or the db2nosql (MongoDB syntax).\n",
    "\n",
    "In the event you believe that a document is corrupted for some reason, you can use the BSON_VALIDATE to make sure it\n",
    "is okay (or not!). The function will return a value of 1 if the record is okay, or a zero otherwise. The one row\n",
    "that we have inserted into the TESTJSON table should be okay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT BSON_VALIDATE(JSON_FIELD) FROM TESTJSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following SQL will inject a bad value into the beginning of the JSON field to test the results from the \n",
    "BSON_VALIDATE funtion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE TESTJSON\n",
    "  SET JSON_FIELD = BLOB('!') || JSON_FIELD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The BSON_VALIDATE should return a zero for this particular row since it is not a valid BSON document."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT BSON_VALIDATE(JSON_FIELD) FROM TESTJSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='manipulating'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manipulating JSON Documents \n",
    "\n",
    "The last section described how we can insert and retrieve entire JSON documents from a column in a table. This\n",
    "section will explore a number of functions that allow access to individual fields within the JSON document. These\n",
    "functions are:\n",
    "\n",
    "- **JSON_VAL** - Extracts data from a JSON document into SQL data types\n",
    "- **JSON_TABLE** - Returns a table of values for a document that has array types in it\n",
    "- **JSON_TYPE** - Returns documents that have a field with a specific data type (like array, or date)\n",
    "- **JSON_LEN** - Returns the count of elements in an array type inside a document\n",
    "- **JSON_GET_POS_ARR_INDEX** - Retrieve the index of a value within an array type in a document\n",
    "\n",
    "Our examples in this section will require a couple of tables to be created.\n",
    "\n",
    "## Sample JSON Table Creation \n",
    "\n",
    "The following SQL will load the JSON_EMP table with a number of JSON objects. These records are modelled \n",
    "around the SAMPLE database JSON_EMP table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE JSON_EMP;\n",
    "\n",
    "CREATE TABLE JSON_EMP\n",
    "  (\n",
    "  SEQ INT NOT NULL GENERATED ALWAYS AS IDENTITY,\n",
    "  EMP_DATA BLOB(4000) INLINE LENGTH 4000\n",
    "  );\n",
    "\n",
    "INSERT INTO JSON_EMP(EMP_DATA) VALUES \n",
    "  JSON2BSON( '{ \"empno\":\"000010\", \"firstnme\":\"CHRISTINE\", \"midinit\":\"I\", \"lastname\":\"HAAS\", \"workdept\":\"A00\", \"phoneno\":[3978], \"hiredate\":\"01/01/1995\", \"job\":\"PRES\", \"edlevel\":18, \"sex\":\"F\", \"birthdate\":\"08/24/1963\", \"pay\" : { \"salary\":152750.00, \"bonus\":1000.00, \"comm\":4220.00} }'),\n",
    "  JSON2BSON( '{\"empno\":\"000020\",\"firstnme\":\"MICHAEL\",\"lastname\":\"THOMPSON\", \"workdept\":\"B01\",\"phoneno\":[3476,1422],\"hiredate\":\"10/10/2003\", \"job\":\"MANAGER\",\"edlevel\":18,\"sex\":\"M\",\"birthdate\":\"02/02/1978\", \"pay\": {\"salary\":94250.00,\"bonus\":800.00,\"comm\":3300.00}}'),\n",
    "  JSON2BSON( '{\"empno\":\"000030\",\"firstnme\":\"SALLY\",\"midinit\":\"A\",\"lastname\":\"KWAN\", \"workdept\":\"C01\",\"phoneno\":[4738],\"hiredate\":\"04/05/2005\", \"job\":\"MANAGER\",\"edlevel\":20,\"sex\":\"F\",\"birthdate\":\"05/11/1971\", \"pay\": {\"salary\":98250.00,\"bonus\":800.00,\"comm\":3060.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000050\",\"firstnme\":\"JOHN\",\"midinit\":\"B\",\"lastname\":\"GEYER\", \"workdept\":\"E01\",\"phoneno\":[6789],\"hiredate\":\"08/17/1979\", \"job\":\"MANAGER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"09/15/1955\", \"pay\": {\"salary\":80175.00,\"bonus\":800.00,\"comm\":3214.00} }'), \n",
    "  JSON2BSON( '{ \"empno\":\"000060\",\"firstnme\":\"IRVING\",\"lastname\":\"STERN\", \"workdept\":\"D11\",\"phoneno\":[6423,2433],\"hiredate\":\"09/14/2003\", \"job\":\"MANAGER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"07/07/1975\", \"pay\": {\"salary\":72250.00,\"bonus\":500.00,\"comm\":2580.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000070\",\"firstnme\":\"EVA\",\"midinit\":\"D\",\"lastname\":\"PULASKI\", \"workdept\":\"D21\",\"phoneno\":[7831,1422,4567],\"hiredate\":\"09/30/2005\", \"job\":\"MANAGER\",\"edlevel\":16,\"sex\":\"F\",\"birthdate\":\"05/26/2003\", \"pay\": {\"salary\":96170.00,\"bonus\":700.00,\"comm\":2893.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000090\",\"firstnme\":\"EILEEN\",\"midinit\":\"W\",\"lastname\":\"HENDERSON\", \"workdept\":\"E11\",\"phoneno\":[5498],\"hiredate\":\"08/15/2000\", \"job\":\"MANAGER\",\"edlevel\":16,\"sex\":\"F\",\"birthdate\":\"05/15/1971\", \"pay\": {\"salary\":89750.00,\"bonus\":600.00,\"comm\":2380.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000100\",\"firstnme\":\"THEODORE\",\"midinit\":\"Q\",\"lastname\":\"SPENSER\", \"workdept\":\"E21\",\"phoneno\":[0972],\"hiredate\":\"06/19/2000\", \"job\":\"MANAGER\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"12/18/1980\", \"pay\": {\"salary\":86150.00,\"bonus\":500.00,\"comm\":2092.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000110\",\"firstnme\":\"VINCENZO\",\"midinit\":\"G\",\"lastname\":\"LUCCHESSI\", \"workdept\":\"A00\",\"phoneno\":[3490,3567],\"hiredate\":\"05/16/1988\", \"job\":\"SALESREP\",\"edlevel\":19,\"sex\":\"M\",\"birthdate\":\"11/05/1959\", \"pay\": {\"salary\":66500.00,\"bonus\":900.00,\"comm\":3720.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000120\",\"firstnme\":\"SEAN\",\"midinit\":\"\",\"lastname\":\"O''CONNELL\", \"workdept\":\"A00\",\"phoneno\":[2167,1533],\"hiredate\":\"12/05/1993\", \"job\":\"CLERK\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"10/18/1972\", \"pay\": {\"salary\":49250.00,\"bonus\":600.00,\"comm\":2340.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000130\",\"firstnme\":\"DELORES\",\"midinit\":\"M\",\"lastname\":\"QUINTANA\", \"workdept\":\"C01\",\"phoneno\":[4578],\"hiredate\":\"07/28/2001\", \"job\":\"ANALYST\",\"edlevel\":16,\"sex\":\"F\",\"birthdate\":\"09/15/1955\", \"pay\": {\"salary\":73800.00,\"bonus\":500.00,\"comm\":1904.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000140\",\"firstnme\":\"HEATHER\",\"midinit\":\"A\",\"lastname\":\"NICHOLLS\", \"workdept\":\"C01\",\"phoneno\":[1793],\"hiredate\":\"12/15/2006\", \"job\":\"ANALYST\",\"edlevel\":18,\"sex\":\"F\",\"birthdate\":\"01/19/1976\", \"pay\": {\"salary\":68420.00,\"bonus\":600.00,\"comm\":2274.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000150\",\"firstnme\":\"BRUCE\",\"midinit\":\"\",\"lastname\":\"ADAMSON\", \"workdept\":\"D11\",\"phoneno\":[4510],\"hiredate\":\"02/12/2002\", \"job\":\"DESIGNER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"05/17/1977\", \"pay\": {\"salary\":55280.00,\"bonus\":500.00,\"comm\":2022.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000160\",\"firstnme\":\"ELIZABETH\",\"midinit\":\"R\",\"lastname\":\"PIANKA\", \"workdept\":\"D11\",\"phoneno\":[3782,9322],\"hiredate\":\"10/11/2006\", \"job\":\"DESIGNER\",\"edlevel\":17,\"sex\":\"F\",\"birthdate\":\"04/12/1980\", \"pay\": {\"salary\":62250.00,\"bonus\":400.00,\"comm\":1780.00} }'),   \n",
    "  JSON2BSON( '{ \"empno\":\"000170\",\"firstnme\":\"MASATOSHI\",\"midinit\":\"J\",\"lastname\":\"YOSHIMURA\", \"workdept\":\"D11\",\"phoneno\":[2890],\"hiredate\":\"09/15/1999\", \"job\":\"DESIGNER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"01/05/1981\", \"pay\": {\"salary\":44680.00,\"bonus\":500.00,\"comm\":1974.00} }'),  \n",
    "  JSON2BSON( '{ \"empno\":\"000180\",\"firstnme\":\"MARILYN\",\"midinit\":\"S\",\"lastname\":\"SCOUTTEN\", \"workdept\":\"D11\",\"phoneno\":[1682,9945],\"hiredate\":\"07/07/2003\", \"job\":\"DESIGNER\",\"edlevel\":17,\"sex\":\"F\",\"birthdate\":\"02/21/1979\", \"pay\": {\"salary\":51340.00,\"bonus\":500.00,\"comm\":1707.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000190\",\"firstnme\":\"JAMES\",\"midinit\":\"H\",\"lastname\":\"WALKER\", \"workdept\":\"D11\",\"phoneno\":[2986,3644],\"hiredate\":\"07/26/2004\", \"job\":\"DESIGNER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"06/25/1982\", \"pay\": {\"salary\":50450.00,\"bonus\":400.00,\"comm\":1636.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000200\",\"firstnme\":\"DAVID\",\"midinit\":\"\",\"lastname\":\"BROWN\", \"workdept\":\"D11\",\"phoneno\":[4501,2522],\"hiredate\":\"03/03/2002\", \"job\":\"DESIGNER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"05/29/1971\", \"pay\": {\"salary\":57740.00,\"bonus\":600.00,\"comm\":2217.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000210\",\"firstnme\":\"WILLIAM\",\"midinit\":\"T\",\"lastname\":\"JONES\", \"workdept\":\"\",\"phoneno\":[0942],\"hiredate\":\"04/11/1998\", \"job\":\"DESIGNER\",\"edlevel\":17,\"sex\":\"M\",\"birthdate\":\"02/23/2003\", \"pay\": {\"salary\":68270.00,\"bonus\":400.00,\"comm\":1462.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000220\",\"firstnme\":\"JENNIFER\",\"midinit\":\"K\",\"lastname\":\"LUTZ\", \"workdept\":\"D11\",\"phoneno\":[0672],\"hiredate\":\"08/29/1998\", \"job\":\"DESIGNER\",\"edlevel\":18,\"sex\":\"F\",\"birthdate\":\"03/19/1978\", \"pay\": {\"salary\":49840.00,\"bonus\":600.00,\"comm\":2387.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000230\",\"firstnme\":\"JAMES\",\"midinit\":\"J\",\"lastname\":\"JEFFERSON\", \"workdept\":\"D21\",\"phoneno\":[2094,8999,3756],\"hiredate\":\"11/21/1996\", \"job\":\"CLERK\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"05/30/1980\", \"pay\": {\"salary\":42180.00,\"bonus\":400.00,\"comm\":1774.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000240\",\"firstnme\":\"SALVATORE\",\"midinit\":\"M\",\"lastname\":\"MARINO\", \"workdept\":\"D21\",\"phoneno\":[3780],\"hiredate\":\"12/05/2004\", \"job\":\"CLERK\",\"edlevel\":17,\"sex\":\"M\",\"birthdate\":\"03/31/2002\", \"pay\": {\"salary\":48760.00,\"bonus\":600.00,\"comm\":2301.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000250\",\"firstnme\":\"DANIEL\",\"midinit\":\"S\",\"lastname\":\"SMITH\", \"workdept\":\"D21\",\"phoneno\":[0961],\"hiredate\":\"10/30/1999\", \"job\":\"CLERK\",\"edlevel\":15,\"sex\":\"M\",\"birthdate\":\"11/12/1969\", \"pay\": {\"salary\":49180.00,\"bonus\":400.00,\"comm\":1534.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000260\",\"firstnme\":\"SYBIL\",\"midinit\":\"P\",\"lastname\":\"JOHNSON\", \"workdept\":\"D21\",\"phoneno\":[8953,2533],\"hiredate\":\"09/11/2005\", \"job\":\"CLERK\",\"edlevel\":16,\"sex\":\"F\",\"birthdate\":\"10/05/1976\", \"pay\": {\"salary\":47250.00,\"bonus\":300.00,\"comm\":1380.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000270\",\"firstnme\":\"MARIA\",\"midinit\":\"L\",\"lastname\":\"PEREZ\", \"workdept\":\"D21\",\"phoneno\":[9001],\"hiredate\":\"09/30/2006\", \"job\":\"CLERK\",\"edlevel\":15,\"sex\":\"F\",\"birthdate\":\"05/26/2003\", \"pay\": {\"salary\":37380.00,\"bonus\":500.00,\"comm\":2190.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000280\",\"firstnme\":\"ETHEL\",\"midinit\":\"R\",\"lastname\":\"SCHNEIDER\", \"workdept\":\"E11\",\"phoneno\":[8997,1422],\"hiredate\":\"03/24/1997\", \"job\":\"OPERATOR\",\"edlevel\":17,\"sex\":\"F\",\"birthdate\":\"03/28/1976\", \"pay\": {\"salary\":36250.00,\"bonus\":500.00,\"comm\":2100.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000290\",\"firstnme\":\"JOHN\",\"midinit\":\"R\",\"lastname\":\"PARKER\", \"workdept\":\"E11\",\"phoneno\":[4502],\"hiredate\":\"05/30/2006\", \"job\":\"OPERATOR\",\"edlevel\":12,\"sex\":\"M\",\"birthdate\":\"07/09/1985\", \"pay\": {\"salary\":35340.00,\"bonus\":300.00,\"comm\":1227.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000300\",\"firstnme\":\"PHILIP\",\"midinit\":\"X\",\"lastname\":\"SMITH\", \"workdept\":\"E11\",\"phoneno\":[2095],\"hiredate\":\"06/19/2002\", \"job\":\"OPERATOR\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"10/27/1976\", \"pay\": {\"salary\":37750.00,\"bonus\":400.00,\"comm\":1420.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000310\",\"firstnme\":\"MAUDE\",\"midinit\":\"F\",\"lastname\":\"SETRIGHT\", \"workdept\":\"E11\",\"phoneno\":[3332,8005],\"hiredate\":\"09/12/1994\", \"job\":\"OPERATOR\",\"edlevel\":12,\"sex\":\"F\",\"birthdate\":\"04/21/1961\", \"pay\": {\"salary\":35900.00,\"bonus\":300.00,\"comm\":1272.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000320\",\"firstnme\":\"RAMLAL\",\"midinit\":\"V\",\"lastname\":\"MEHTA\", \"workdept\":\"E21\",\"phoneno\":[9990,1533],\"hiredate\":\"07/07/1995\", \"job\":\"FIELDREP\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"08/11/1962\", \"pay\": {\"salary\":39950.00,\"bonus\":400.00,\"comm\":1596.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000330\",\"firstnme\":\"WING\",\"midinit\":\"\",\"lastname\":\"LEE\", \"workdept\":\"E21\",\"phoneno\":[2103,2453],\"hiredate\":\"02/23/2006\", \"job\":\"FIELDREP\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"07/18/1971\", \"pay\": {\"salary\":45370.00,\"bonus\":500.00,\"comm\":2030.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"000340\",\"firstnme\":\"JASON\",\"midinit\":\"R\",\"lastname\":\"GOUNOT\", \"workdept\":\"E21\",\"phoneno\":[5698,7744],\"hiredate\":\"05/05/1977\", \"job\":\"FIELDREP\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"05/17/1956\", \"pay\": {\"salary\":43840.00,\"bonus\":500.00,\"comm\":1907.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200010\",\"firstnme\":\"DIAN\",\"midinit\":\"J\",\"lastname\":\"HEMMINGER\", \"workdept\":\"A00\",\"phoneno\":[3978,2564],\"hiredate\":\"01/01/1995\", \"job\":\"SALESREP\",\"edlevel\":18,\"sex\":\"F\",\"birthdate\":\"08/14/1973\", \"pay\": {\"salary\":46500.00,\"bonus\":1000.00,\"comm\":4220.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200120\",\"firstnme\":\"GREG\",\"midinit\":\"\",\"lastname\":\"ORLANDO\", \"workdept\":\"A00\",\"phoneno\":[2167,1690],\"hiredate\":\"05/05/2002\", \"job\":\"CLERK\",\"edlevel\":14,\"sex\":\"M\",\"birthdate\":\"10/18/1972\", \"pay\": {\"salary\":39250.00,\"bonus\":600.00,\"comm\":2340.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200140\",\"firstnme\":\"KIM\",\"midinit\":\"N\",\"lastname\":\"NATZ\", \"workdept\":\"C01\",\"phoneno\":[1793],\"hiredate\":\"12/15/2006\", \"job\":\"ANALYST\",\"edlevel\":18,\"sex\":\"F\",\"birthdate\":\"01/19/1976\", \"pay\": {\"salary\":68420.00,\"bonus\":600.00,\"comm\":2274.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200170\",\"firstnme\":\"KIYOSHI\",\"midinit\":\"\",\"lastname\":\"YAMAMOTO\", \"workdept\":\"D11\",\"phoneno\":[2890],\"hiredate\":\"09/15/2005\", \"job\":\"DESIGNER\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"01/05/1981\", \"pay\": {\"salary\":64680.00,\"bonus\":500.00,\"comm\":1974.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200220\",\"firstnme\":\"REBA\",\"midinit\":\"K\",\"lastname\":\"JOHN\", \"workdept\":\"D11\",\"phoneno\":[0672],\"hiredate\":\"08/29/2005\", \"job\":\"DESIGNER\",\"edlevel\":18,\"sex\":\"F\",\"birthdate\":\"03/19/1978\", \"pay\": {\"salary\":69840.00,\"bonus\":600.00,\"comm\":2387.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200240\",\"firstnme\":\"ROBERT\",\"midinit\":\"M\",\"lastname\":\"MONTEVERDE\", \"workdept\":\"D21\",\"phoneno\":[3780,6823],\"hiredate\":\"12/05/2004\", \"job\":\"CLERK\",\"edlevel\":17,\"sex\":\"M\",\"birthdate\":\"03/31/1984\", \"pay\": {\"salary\":37760.00,\"bonus\":600.00,\"comm\":2301.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200280\",\"firstnme\":\"EILEEN\",\"midinit\":\"R\",\"lastname\":\"SCHWARTZ\", \"workdept\":\"E11\",\"phoneno\":[8997,9410],\"hiredate\":\"03/24/1997\", \"job\":\"OPERATOR\",\"edlevel\":17,\"sex\":\"F\",\"birthdate\":\"03/28/1966\", \"pay\": {\"salary\":46250.00,\"bonus\":500.00,\"comm\":2100.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200310\",\"firstnme\":\"MICHELLE\",\"midinit\":\"F\",\"lastname\":\"SPRINGER\", \"workdept\":\"E11\",\"phoneno\":[3332,7889],\"hiredate\":\"09/12/1994\", \"job\":\"OPERATOR\",\"edlevel\":12,\"sex\":\"F\",\"birthdate\":\"04/21/1961\", \"pay\": {\"salary\":35900.00,\"bonus\":300.00,\"comm\":1272.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200330\",\"firstnme\":\"HELENA\",\"midinit\":\"\",\"lastname\":\"WONG\", \"workdept\":\"E21\",\"phoneno\":[2103],\"hiredate\":\"02/23/2006\", \"job\":\"FIELDREP\",\"edlevel\":14,\"sex\":\"F\",\"birthdate\":\"07/18/1971\", \"pay\": {\"salary\":35370.00,\"bonus\":500.00,\"comm\":2030.00} }'),\n",
    "  JSON2BSON( '{ \"empno\":\"200340\",\"firstnme\":\"ROY\",\"midinit\":\"R\",\"lastname\":\"ALONZO\", \"workdept\":\"E21\",\"phoneno\":[5698,1533],\"hiredate\":\"07/05/1997\", \"job\":\"FIELDREP\",\"edlevel\":16,\"sex\":\"M\",\"birthdate\":\"05/17/1956\", \"pay\": {\"salary\":31840.00,\"bonus\":500.00,\"comm\":1907.00} }')\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can check the count of records to make sure that 42 employees were added to our table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT COUNT(*) FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional DEPARTMENT Table\n",
    "  \n",
    "In addition to the JSON_EMP table, the following SQL will generate a traditional table called JSON_DEPT\n",
    "that can be used to determine the name of the department an individual works in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE JSON_DEPT;\n",
    "  \n",
    "CREATE TABLE JSON_DEPT\n",
    "  (\n",
    "  SEQ INT NOT NULL GENERATED ALWAYS AS IDENTITY,\n",
    "  DEPT_DATA BLOB(4000) INLINE LENGTH 4000 \n",
    "  );\n",
    "     \n",
    "INSERT INTO JSON_DEPT(DEPT_DATA) VALUES\n",
    "  JSON2BSON('{\"deptno\":\"A00\", \"mgrno\":\"000010\", \"admrdept\":\"A00\", \"deptname\":\"SPIFFY COMPUTER SERVICE DIV.\"}'),\n",
    "  JSON2BSON('{\"deptno\":\"B01\", \"mgrno\":\"000020\", \"admrdept\":\"A00\", \"deptname\":\"PLANNING\"                    }'),\n",
    "  JSON2BSON('{\"deptno\":\"C01\", \"mgrno\":\"000030\", \"admrdept\":\"A00\", \"deptname\":\"INFORMATION CENTER\"          }'),\n",
    "  JSON2BSON('{\"deptno\":\"D01\",                   \"admrdept\":\"A00\", \"deptname\":\"DEVELOPMENT CENTER\"          }'),\n",
    "  JSON2BSON('{\"deptno\":\"D11\", \"mgrno\":\"000060\", \"admrdept\":\"D01\", \"deptname\":\"MANUFACTURING SYSTEMS\"       }'),\n",
    "  JSON2BSON('{\"deptno\":\"D21\", \"mgrno\":\"000070\", \"admrdept\":\"D01\", \"deptname\":\"ADMINISTRATION SYSTEMS\"      }'),\n",
    "  JSON2BSON('{\"deptno\":\"E01\", \"mgrno\":\"000050\", \"admrdept\":\"A00\", \"deptname\":\"SUPPORT SERVICES\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"E11\", \"mgrno\":\"000090\", \"admrdept\":\"E01\", \"deptname\":\"OPERATIONS\"                  }'),\n",
    "  JSON2BSON('{\"deptno\":\"E21\", \"mgrno\":\"000100\", \"admrdept\":\"E01\", \"deptname\":\"SOFTWARE SUPPORT\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"F22\",                   \"admrdept\":\"E01\", \"deptname\":\"BRANCH OFFICE F2\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"G22\",                   \"admrdept\":\"E01\", \"deptname\":\"BRANCH OFFICE G2\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"H22\",                   \"admrdept\":\"E01\", \"deptname\":\"BRANCH OFFICE H2\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"I22\",                   \"admrdept\":\"E01\", \"deptname\":\"BRANCH OFFICE I2\"            }'),\n",
    "  JSON2BSON('{\"deptno\":\"J22\",                   \"admrdept\":\"E01\", \"deptname\":\"BRANCH OFFICE J2\"            }')\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='retrieving'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving Data from a BSON Document\n",
    "\n",
    "Now that we have inserted some JSON data into a table, this section will explore \n",
    "the use of the JSON_VAL function to retrieve individual fields from the documents. \n",
    "This built-in function will return a value from a document in a format that you specify. \n",
    "The ability to dynamically change the returned data type is extremely important when we \n",
    "examine index creation in another section.\n",
    "\n",
    "The JSON_VAL function has the format:\n",
    "\n",
    "<pre>\n",
    "JSON_VAL(document, field, type)\n",
    "</pre>\n",
    "\n",
    "JSON_VAL takes 3 arguments:\n",
    "- *document* - BSON document\n",
    "- *field* - The field we are looking for (search path)\n",
    "- *type* - The return type of data being returned\n",
    "\n",
    "The search path and type must be constants - they cannot be variables so their \n",
    "use in user-defined functions is limited to using constants.\n",
    "\n",
    "A typical JSON record will contain a variety of data types and structures as \n",
    "illustrated by the following record from the JSON_EMP table.\n",
    "\n",
    "\n",
    "<pre>\n",
    "  {\n",
    "    \"empno\":\"200170\",\n",
    "    \"firstnme\":\"KIYOSHI\",\n",
    "    \"midinit\":\"\",\n",
    "    \"lastname\":\"YAMAMOTO\",\n",
    "    \"workdept\":\"D11\",\n",
    "    \"phoneno\":[2890],\n",
    "    \"hiredate\":\"09/15/2005\",\n",
    "    \"job\":\"DESIGNER\",\n",
    "    \"edlevel\":16,\n",
    "    \"sex\":\"M\",\n",
    "    \"birthdate\":\"01/05/1981\",\n",
    "    \"pay\": {\n",
    "      \"salary\":64680.00,\n",
    "      \"bonus\":500.00,\n",
    "      \"comm\":1974.00\n",
    "    }\n",
    "  } \n",
    "</pre>\n",
    "\n",
    "There are number of fields with different formats, including strings (firstnme), \n",
    "integers (edlevel), decimal (salary), date (hiredate), a number array (phoneno), \n",
    "and a structure (pay). JSON data can consist of nested objects, arrays and very \n",
    "complex structures. The format of a JSON object is checked when using the JSON2BSON \n",
    "function and an error message will be issued if it does not conform to the \n",
    "JSON specification.\n",
    "\n",
    "The JSON_VAL function needs to know how to return the data type back from the JSON \n",
    "record, so you need to specify what the format should be. The possible formats are:\n",
    "\n",
    "| Code  | Format                                        |\n",
    "|:------|:----------------------------------------------|\n",
    "| n     | DECFLOAT                                      |\n",
    "| i     | INTEGER                                       |\n",
    "| I     | BIGINT (notice this is a lowercase L)         |\n",
    "| f     | DOUBLE                                        |\n",
    "| d     | DATE                                          |\n",
    "| ts    | TIMESTAMP (6)                                 |\n",
    "| t     | TIME                                          |\n",
    "| s:n   | A VARCHAR with a size of n being the maximum  |\n",
    "| b:n   | A BINARY value with n being the maximum       |\n",
    "| u     | Null check (0=null 1=not null)                |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='atomic'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving Atomic Values\n",
    "\n",
    "This first example will retrieve the name and salary of the employee whose employee \n",
    "number is \"200170\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT trim(JSON_VAL(EMP_DATA,'lastname','s:40')), \n",
    "       JSON_VAL(EMP_DATA,'pay.salary','f')\n",
    "  FROM JSON_EMP\n",
    "WHERE\n",
    "  JSON_VAL(EMP_DATA,'empno','s:6') = '200170'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the size of the field being returned is larger that the field specification, \n",
    "you will get a NULL value returned, not a truncated value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(EMP_DATA,'lastname','s:7')\n",
    "  FROM JSON_EMP\n",
    "WHERE\n",
    "  JSON_VAL(EMP_DATA,'empno','s:6') = '000010'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the case of character fields, you may need to specify a larger return \n",
    "size and then truncate it to get a subset of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT LEFT(JSON_VAL(EMP_DATA,'lastname','s:20'),7)\n",
    "  FROM JSON_EMP\n",
    "WHERE\n",
    "  JSON_VAL(EMP_DATA,'empno','s:6') = '200170'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='array'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving Array Values\n",
    "\n",
    "Selecting data from an array type will always give you the first value (element zero). \n",
    "The employees all have extension numbers but some of them have more than one. \n",
    "Some of the extensions start with a zero so since the column is being treated as an \n",
    "integer you will get only 3 digits. It's probably better to define it as a character \n",
    "string rather than a number!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(EMP_DATA, 'phoneno.2', 'i') FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you specify \":na\" after the type specifier, you will get an error if the field \n",
    "is an array type. Hopefully you already know the format of your JSON data and can \n",
    "avoid having to check to see if arrays exist. What this statement will tell you is \n",
    "that one of the records you were attempting to retrieve was an array type. In fact, \n",
    "all the phone extensions are being treated as array types even though they have only \n",
    "one value in many cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(EMP_DATA, 'phoneno', 'i:na') FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you need to access a specific array element in a field, you can use the \"dot\" \n",
    "notation after the field name. The first element starts at zero. If we select \n",
    "the 2nd element (.1) all the employees that have a second extension will have a \n",
    "value retrieved while the ones who don't will have a null value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='structures'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving Structured Fields\n",
    "\n",
    "Structured fields are retrieved using the same dot notation as arrays. \n",
    "The field is specified by using the \"field.subfield\" format and these fields can be \n",
    "an arbitrary number of levels deep. \n",
    "\n",
    "The pay field in the employee record is made up of three additional fields.\n",
    "<pre> \n",
    "\"pay\": {\n",
    "   \"salary\":64680.00,\n",
    "   \"bonus\":500.00,\n",
    "   \"comm\":1974.00\n",
    "}\n",
    "</pre>\n",
    "\n",
    "To retrieve these three fields, you need to explictly name them since \n",
    "retrieving pay alone will not work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(EMP_DATA,'pay.salary','i'),\n",
    "       JSON_VAL(EMP_DATA,'pay.bonus','i'),\n",
    "       JSON_VAL(EMP_DATA,'pay.comm','i')\n",
    "  FROM JSON_EMP\n",
    "WHERE\n",
    "  JSON_VAL(EMP_DATA,'empno','s:6') = '200170'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you attempt to retrieve the pay field, you will end up with a NULL value, not \n",
    "an error code. The reason for this is that the JSON_VAL function cannot format the \n",
    "field into an atomic value so it returns the NULL value instead.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='nulls'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Determining NULL Values in a Field\n",
    "\n",
    "To determine whether a field exists, or has a null value, you need use the \"u\" flag.  \n",
    "If you use the \"u\" flag, the value returned will be either:\n",
    "- **1** - The field exists, and it has a value (not null or empty string)\n",
    "- **0** - The field exists, but the value is null or empty\n",
    "- **null** - The field does not exist\n",
    "\n",
    "In the JSON_EMP table, there are a few employees who do not have middle names. \n",
    "The following query will return a value or 1, 0, or NULL depending on whether the \n",
    "middle name exists for a record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(EMP_DATA,'lastname','s:30'),\n",
    "       JSON_VAL(EMP_DATA,'midinit','u')\n",
    "FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results contain 40 employees who have a middle initial, and two that do not. \n",
    "The results can be misleading because an employee can have the midinit field defined, \n",
    "but no value assigned to it:\n",
    "\n",
    "<pre>\n",
    "{\n",
    " \"empno\":\"000120\",\n",
    " \"firstnme\":\"SEAN\",\n",
    " \"midinit\":\"\",\n",
    " \"lastname\":\"O''CONNELL\",...\n",
    "}  \n",
    "</pre>    \n",
    " \n",
    "In this case, the employee does not have a middle name, but the field is present. \n",
    "To determine whether an employee does not have a middle name, you will need to check \n",
    "for a NULL value (the field does not exist, or the field is empty) when \n",
    "retrieving the middle initial (9 rows):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM JSON_EMP\n",
    "  WHERE JSON_VAL(EMP_DATA,'midinit','s:40') IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you only want to know how many employee have the middle initial field (midinit) \n",
    "that is empty, you need to exclude the records that do not contain the field (7 rows):\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM JSON_EMP\n",
    "  WHERE JSON_VAL(EMP_DATA,'midinit','s:40') IS NULL AND\n",
    "        JSON_VAL(EMP_DATA,'midinit','u') IS NOT NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='joins'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining JSON Tables \n",
    "\n",
    "You can join tables with JSON columns by using the JSON_VAL function \n",
    "to compare two values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(EMP_DATA,'empno','s:6') AS EMPNO, \n",
    "       JSON_VAL(EMP_DATA,'lastname','s:20') AS LASTNAME,\n",
    "       JSON_VAL(DEPT_DATA,'deptname','s:30') AS DEPTNAME\n",
    "  FROM JSON_EMP, JSON_DEPT\n",
    "WHERE\n",
    "  JSON_VAL(DEPT_DATA,'deptno','s:3') = \n",
    "  JSON_VAL(EMP_DATA,'workdept','s:3')\n",
    "FETCH FIRST 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "You need to ensure that the data types from both JSON functions are compatible for \n",
    "the join to work properly. In this case, the department number and the work department \n",
    "are both returned as 3-byte character strings. If you decided to use integers \n",
    "instead or a smaller string size, the join will not work as expected because \n",
    "the conversion will result in truncated or NULL values.\n",
    "\n",
    "If you plan on doing joins between JSON objects, you may want to consider creating \n",
    "indexes on the documents to speed up the join process. More information on the use \n",
    "of indexes is found at the end of this chapter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='datatypes'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# JSON Data Types \n",
    "\n",
    "If you are unsure of what data type a field contains, you can use the the JSON_TYPE \n",
    "function to determine the type before retrieving the field.\n",
    "\n",
    "The JSON_TYPE function has the format:\n",
    "\n",
    "<pre>\n",
    "ID = JSON_TYPE(document, field, 2048)\n",
    "</pre>\n",
    "\n",
    "JSON_TYPE takes 3 arguments:\n",
    "- *document* - BSON document\n",
    "- *field* - The field we are looking for (search path)\n",
    "- *search path size* - 2048 is the required value\n",
    "\n",
    "The 2048 specifies the maximum length of the field parameter and should be \n",
    "left at this value.\n",
    "\n",
    "When querying the data types within a JSON document, the following values are returned.\n",
    "\n",
    "ID  |\tTYPE             |    \tID  |\tTYPE\n",
    "---:| :------------------| -------: | :---------------------\n",
    " 1  |\tDouble           |    \t10  |\tNull      \n",
    " 2  |\tString           |    \t11  |\tRegular Expression\n",
    " 3  |\tObject           |    \t12  |\tFuture use\n",
    " 4  |\tArray            |    \t13  |\tJavaScript\n",
    " 5  |\tBinary data      |    \t14  |\tSymbol\n",
    " 6  |\tUndefined        |    \t15  |\tJavascript (with scope)\n",
    " 7  |\tObject id        |    \t16  |\t32-bit integer\n",
    " 8  |\tBoolean          |    \t17  |\tTimestamp \n",
    " 9  |\tDate             |    \t18  |\t64-bit integer\n",
    "\n",
    "The next SQL statement will create a table with standard types within it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE TYPES;\n",
    "\n",
    "CREATE TABLE TYPES\n",
    "  (DATA BLOB(4000) INLINE LENGTH 4000);\n",
    "\n",
    "INSERT INTO TYPES VALUES\n",
    "  JSON2BSON(\n",
    "  '{\n",
    "  \"string\"    : \"string\",\n",
    "  \"integer\"   : 1,\n",
    "  \"number\"    : 1.1,\n",
    "  \"date\"      : {\"$date\": \"2016-06-20T13:00:00\"},\n",
    "  \"boolean\"   : true,\n",
    "  \"array\"     : [1,2,3],\n",
    "  \"object\"    : {type: \"main\", phone: [1,2,3]}\n",
    "  }');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following SQL will generate a list of data types and field names found within this document.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT 'STRING',JSON_TYPE(DATA, 'string', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'INTEGER',JSON_TYPE(DATA, 'integer', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'NUMBER',JSON_TYPE(DATA, 'number', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'DATE',JSON_TYPE(DATA, 'date', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'BOOLEAN', JSON_TYPE(DATA, 'boolean', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'ARRAY', JSON_TYPE(DATA, 'array', 2048) FROM TYPES\n",
    "UNION ALL\n",
    "SELECT 'OBJECT', JSON_TYPE(DATA, 'object', 2048) FROM TYPES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following sections will show how we can get atomic (non-array) types out of \n",
    "a JSON document. We are not going to be specific which documents we want, other \n",
    "than what field we want to retrieve.\n",
    "\n",
    "A temporary table called SANDBOX is used throughout these examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE SANDBOX;\n",
    "\n",
    "CREATE TABLE SANDBOX (DATA BLOB(4000) INLINE LENGTH 4000);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='integers'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON INTEGERS and BIGINT\n",
    "\n",
    "Integers within JSON documents are easily identified as numbers that don't have a \n",
    "decimal places in them. There are two different types of integers supported \n",
    "within Db2 and are identified by the size (number of digits) in the number itself.\n",
    "\n",
    "- **Integer** - A set of digits that do not include a decimal place. The number cannot exceed -2,147,483,648 to 2,147,483,647.\n",
    "- **Bigint**  - A set of digits that do not include a decimal place but exceed that of an integer. The number cannot exceed -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.\n",
    "\n",
    "You don't explicitly state the type of integer that you are using. \n",
    "The system will detect the type based on its size.\n",
    "\n",
    "The JSON_TYPE function will return a value of 16 for integers and 18 for a \n",
    "large integer (BIGINT). To retrieve a value from an integer field you need to \n",
    "use the \"i\" flag and \"l\" (lowercase L) for big integers.\n",
    "\n",
    "This first SQL statement will create a regular integer field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"count\":9782333}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON_TYPE function will verify that this is an integer field (Type=16)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_TYPE(DATA,'count',2048) AS TYPE \n",
    "  FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can retrieve an integer value with either the 'i' flag or the 'l' flag. \n",
    "This first SQL statement retrieves the value as an integer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'count','i') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can ask that the value be interpreted as a BIGINT by using the 'l' flag, \n",
    "so JSON_VAL will expand the size of the return value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'count','l') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next SQL statement will create a field with a BIGINT size. Note that we don't \n",
    "need to specify anything other than have a very big number!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    "\n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"count\":94123512223422}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON_TYPE function will verify that this is a big integer field (Type=18)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'count',2048) AS TYPE FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can check to see that the data is stored in the document as a BIGINT by \n",
    "using the JSON_TYPE function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'count',2048) FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Returning the data as an integer type 'i' will fail since the number is too big \n",
    "to fit into an integer format. Note that you do not get an error message - \n",
    "a NULL value gets returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'count','i') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specifying the 'I' flag will make the data be returned properly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'count','l') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we have an integer in the JSON field, we also have the option of returning \n",
    "the value as a floating-point number (f) or as a decimal number (n). Either of \n",
    "these options will work with integer values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(DATA,'count','n') AS DECIMAL, \n",
    "       JSON_VAL(DATA,'count','f') AS FLOAT\n",
    "FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='numbers'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON NUMBERS and FLOATING POINT\n",
    "\n",
    "JSON numbers are recognized by Db2 when there is a decimal point in the value. \n",
    "Floating point values are recognized using the Exx specifier after the number \n",
    "which represents the power of 10 that needs to be applied to the base value. \n",
    "For instance, 1.0E01 is the value 10.\n",
    "\n",
    "The JSON type for numbers is 1, whether it is in floating point format or decimal format.\n",
    "\n",
    "The SQL statement below inserts a salary into the table (using the \n",
    "standard decimal place notation)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"salary\":92342.20}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON_TYPE function will verify that this is a numeric field (Type=1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'salary',2048) AS TYPE FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Numeric data can be retrieved in either number (n) formant, integer (i - note that\n",
    "you will get truncation), or floating point (f)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(DATA,'salary','n') AS DECIMAL, \n",
    "       JSON_VAL(DATA,'salary','i') AS INTEGER,\n",
    "       JSON_VAL(DATA,'salary','f') AS FLOAT\n",
    "FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may wonder why number format (n) results in an answer that has a fractional \n",
    "component that isn't exactly 92342.20. The reason is that Db2 is converting the \n",
    "value to DECFLOAT(34) which supports a higher precision number, but can result in \n",
    "fractions that can't be accurately represented within the binary format. Casting \n",
    "the value to DEC(9,2) will properly format the number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT DEC(JSON_VAL(DATA,'salary','n'),9,2) AS DECIMAL FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A floating-point number is recognized by the Exx specifier in the number. The \n",
    "BSON function will tag this value as a number even though you specified it in floating \n",
    "point format. The following SQL inserts the floating value into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"salary\":9.2523E01}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON_TYPE function will verify that this is a floating point field (Type=1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'salary',2048) AS TYPE FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The floating-point value can be retrieved as a number, integer, or floating point value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(DATA,'salary','n') AS DECIMAL, \n",
    "       JSON_VAL(DATA,'salary','i') AS INTEGER,\n",
    "       JSON_VAL(DATA,'salary','f') AS FLOAT\n",
    "FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='boolean'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON BOOLEAN VALUES\n",
    "\n",
    "JSON has a data type which can be true or false (boolean). Db2 doesn't have an \n",
    "equivalent data type for boolean, so we need to retrieve it as an integer or \n",
    "character string (true/false).\n",
    "\n",
    "The JSON type for boolean values is 8.\n",
    " \n",
    "The SQL statement below inserts a true and false value into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"valid\":true, \"invalid\":false}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will double-check what type the field is in the JSON record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'valid',2048) AS TYPE FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To retrieve the value, we can ask that it be formatted as an integer or number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(DATA,'valid','n') AS TRUE_DECIMAL, \n",
    "       JSON_VAL(DATA,'valid','i') AS TRUE_INTEGER,\n",
    "       JSON_VAL(DATA,'invalid','n') AS FALSE_DECIMAL,\n",
    "       JSON_VAL(DATA,'invalid','i') AS FALSE_INTEGER\n",
    "FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also retrieve a boolean field as a character or\n",
    "binary field, but the results are not what you would expect\n",
    "with binary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(DATA,'valid','s:5') AS TRUE_STRING, \n",
    "       JSON_VAL(DATA,'valid','b:2') AS TRUE_BINARY,\n",
    "       JSON_VAL(DATA,'invalid','s:5') AS FALSE_STRING,\n",
    "       JSON_VAL(DATA,'invalid','b:2') AS FALSE_BINARY\n",
    "FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='date'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON DATE, TIME, and TIMESTAMPS\n",
    "\n",
    "This first SQL statement will insert a JSON field that uses the $date modifier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"today\":{\"$date\":\"2016-07-01T12:00:00\"}}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Querying the data type of this field using JSON_VAL will return a value of 9 (date type)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_TYPE(DATA,'today',2048) FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you decide to use a character string to represent a date, you can use either \n",
    "the \"s:x\" specification to return the date as a string,\n",
    "or use \"d\" to have it displayed as a date. This first SQL\n",
    "statement returns the date as a string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"today\":\"2016-07-01\"}');\n",
    "\n",
    "SELECT JSON_VAL(DATA,'today','s:10') FROM SANDBOX;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the 'd' specification will return the value as a date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'today','d') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What about timestamps? If you decide to store a timestamp into a field, you can \n",
    "retrieve it in a variety of ways. This first set of SQL statements will retrieve \n",
    "it as a string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"today\":\"' || VARCHAR(NOW()) || '\"}');\n",
    "\n",
    "SELECT JSON_VAL(DATA,'today','s:30') FROM SANDBOX;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieving it as a Date will also work, but the time portion will be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'today','d') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also ask for the timestamp value by using the 'ts'\n",
    "specification. Note that you can't get just the time portion\n",
    "unless you use a SQL function to cast it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(DATA,'today','ts') FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To force the value to return just the time portion, either\n",
    "store the data as a time value (HH:MM:SS) string or store a\n",
    "timestamp and use the TIME function to extract just that\n",
    "portion of the timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT TIME(JSON_VAL(DATA,'today','ts')) FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='strings'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# JSON Strings \n",
    "\n",
    "For character strings, you must specify what the maximum\n",
    "length is. This example will return the size of the lastname\n",
    "field as 10 characters long."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(EMP_DATA, 'lastname', 's:10') FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You must specify a length for the 's' parameter otherwise\n",
    "you will get an error from the function. If the size of the\n",
    "character string is too large to return, then the function\n",
    "will return a null value for that field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(EMP_DATA, 'lastname', 's:8') FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='arrays'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with JSON Arrays\n",
    "JSON arrays require specialized handling since there is no easy way to map an array to a single column in a relational table. Instead, there are a number of functions which will convert the array into a table so that you can access the individual elements in an SQL statement."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='table'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing all Elements in an Array\n",
    "\n",
    "The following query works because we do not treat the field phoneno as an array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT JSON_VAL(EMP_DATA, 'phoneno', 'i') FROM JSON_EMP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, only the first number of an array is returned\n",
    "when you use JSON_VAL. However, there will be situations\n",
    "where you do want to return all the values in an array. This\n",
    "is where the JSON_TABLE function must be used.\n",
    "\n",
    "The format of the JSON_TABLE function is:\n",
    "<pre>\n",
    "   JSON_TABLE(document, field, type)\n",
    "</pre>\n",
    "The arguments are:\n",
    "\n",
    "- *document* - BSON document\n",
    "- *field* - The field we are looking for\n",
    "- *type* - The return type of data being returned \n",
    " \n",
    "JSON_TABLE returns two columns: Type and Value. The type\n",
    "is one of a possible 18 values found in the table below. The\n",
    "Value is the actual contents of the field.\n",
    "\n",
    "ID  |\tTYPE             |    \tID  |\tTYPE\n",
    "---:| :------------------| -------: | :---------------------\n",
    " 1  |\tDouble           |    \t10  |\tNull      \n",
    " 2  |\tString           |    \t11  |\tRegular Expression\n",
    " 3  |\tObject           |    \t12  |\tFuture use\n",
    " 4  |\tArray            |    \t13  |\tJavaScript\n",
    " 5  |\tBinary data      |    \t14  |\tSymbol\n",
    " 6  |\tUndefined        |    \t15  |\tJavascript (with scope)\n",
    " 7  |\tObject id        |    \t16  |\t32-bit integer\n",
    " 8  |\tBoolean          |    \t17  |\tTimestamp \n",
    " 9  |\tDate             |    \t18  |\t64-bit integer\n",
    "\n",
    "The TYPE field is probably something you wouldn't require\n",
    "as part of your queries since you are already specifying the\n",
    "return type in the function.\n",
    "\n",
    "The format of the JSON_TABLE function is like JSON_VAL\n",
    "except that it returns a table of values. You must use this\n",
    "function as part of FROM clause and a table function\n",
    "specification. For example, to return the contents of the\n",
    "phone extension array for just one employee (000230) we can\n",
    "use the following JSON_TABLE function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "SELECT PHONES.TYPE, CAST(PHONES.VALUE AS VARCHAR(10)) AS VALUE \n",
    "  FROM JSON_EMP E, \n",
    "       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES \n",
    "WHERE JSON_VAL(E.EMP_DATA,'empno','s:6') = '000010'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The TABLE( ... ) specification in the FROM clause is used\n",
    "for table functions. The results that are returned from the\n",
    "TABLE function are treated the same as a traditional table.\n",
    "\n",
    "To create a query that gives the name of every employee and their extensions would require the following query.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -a\n",
    "SELECT JSON_VAL(E.EMP_DATA, 'lastname', 's:10') AS LASTNAME, \n",
    "       CAST(PHONES.VALUE AS VARCHAR(10)) AS PHONE\n",
    "  FROM JSON_EMP E, \n",
    "       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only a subset of the results is shown above, but you will\n",
    "see that there are multiple lines for employees who have\n",
    "more than one extension.\n",
    "\n",
    "The results of a TABLE function must be named (AS ...) if\n",
    "you need to refer to the results of the TABLE function in\n",
    "the SELECT list or in other parts of the SQL.\n",
    "\n",
    "You can use other SQL operators to sort or organize the\n",
    "results. For instance, we can use the ORDER BY operator to\n",
    "find out which employees have the same extension. Note how\n",
    "the TABLE function is named PHONES and the VALUES column is\n",
    "renamed to PHONE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(E.EMP_DATA, 'lastname', 's:10') AS LASTNAME,\n",
    "       CAST (PHONES.VALUE AS VARCHAR(10)) AS PHONE\n",
    "  FROM JSON_EMP E, \n",
    "       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES\n",
    "ORDER BY PHONE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can even find out how many people are sharing\n",
    "extensions! The HAVING clause tells Db2 to only return\n",
    "groupings where there are more than one employee with the\n",
    "same extension."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT CAST(PHONES.VALUE AS VARCHAR(10)) AS PHONE, COUNT(*) AS COUNT \n",
    "  FROM JSON_EMP E, \n",
    "       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES\n",
    "GROUP BY PHONES.VALUE HAVING COUNT(*) > 1\n",
    "ORDER BY PHONES.VALUE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='length'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Determining the Size of an Array\n",
    "\n",
    "The previous example showed how we could retrieve the\n",
    "values from within an array of a document. Sometimes an\n",
    "application needs to determine how many values are in the\n",
    "array itself. The JSON_LEN function is used to figure out\n",
    "what the array count is. \n",
    "\n",
    "The format of the JSON_LEN function is:\n",
    "<pre>  \n",
    "   count = JSON_LEN(document,field)\n",
    "</pre>\n",
    " \n",
    "The arguments are:\n",
    "\n",
    "- *document* - BSON document\n",
    "- *field* - The field we are looking for\n",
    "- *count* - Number of array entries or NULL if the field is not an array\n",
    "\n",
    "If the field is not an array, this function will return a\n",
    "null value, otherwise it will give you the number of values\n",
    "in the array. In our previous example, we could determine\n",
    "the number of extensions per person by taking advantage of\n",
    "the JSON_LEN function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(E.EMP_DATA, 'lastname', 's:10') AS LASTNAME, \n",
    "       JSON_LEN(E.EMP_DATA, 'phoneno') AS PHONE_COUNT\n",
    "  FROM JSON_EMP E"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='getpos'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding a Value within an Array\n",
    "\n",
    "The JSON_TABLE and JSON_LEN functions can be used to\n",
    "retrieve all the values from an array, but searching for a\n",
    "specific array value is difficult to do. One way to seach\n",
    "array values is to extract everything using the JSON_TABLE\n",
    "function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(E.EMP_DATA, 'lastname', 's:10') AS LASTNAME,\n",
    "       CAST(PHONES.VALUE AS VARCHAR(10)) AS PHONE\n",
    "  FROM JSON_EMP E, \n",
    "       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES\n",
    "WHERE PHONES.VALUE = 1422"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An easier way is to use the JSON_GET_POS_ARR_INDEX function.\n",
    "This function will search array values without having to\n",
    "extract the array values with the JSON_TABLE function.\n",
    "\n",
    "The format of the JSON_GET_POS_ARR_INDEX function is:\n",
    "\n",
    "<pre>  \n",
    "   element = JSON_GET_POS_ARR_INDEX(document, field)\n",
    "</pre>\n",
    "\n",
    "The arguments are: \n",
    "- *document* - BSON document\n",
    "- *field* - The field we are looking for and its value\n",
    "- *element* - The first occurrence of the value in the array\n",
    "\n",
    "The format of the field argument is \"{field:value}\" and it needs to be in\n",
    "BSON format. This means you needs to add the JSON2BSON\n",
    "function around the field specification.\n",
    "\n",
    "<pre>\n",
    "    JSON2BSON( '{\"field\":\"value\"}' )\n",
    "</pre>\n",
    "      \n",
    "This function only tests for equivalence and the data type should match what is\n",
    "already in the field. The return value is the position\n",
    "within the array that the value was found, where the first\n",
    "element starts at zero.\n",
    "\n",
    "In our JSON_EMP table, each employee has one or more phone\n",
    "numbers. The following SQL will retrieve all employees who\n",
    "have the extension 1422:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT JSON_VAL(EMP_DATA, 'lastname', 's:10') AS LASTNAME\n",
    "  FROM JSON_EMP\n",
    "WHERE JSON_GET_POS_ARR_INDEX(EMP_DATA,\n",
    "  JSON2BSON('{\"phoneno\":1422}')) >= 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we used quotes around the phone number, the function will not match any of \n",
    "the values in the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='updating'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Updating JSON Documents\n",
    "\n",
    "There are a couple of approaches available to updating JSON\n",
    "documents. One approach is to extract the document from the\n",
    "table in a text form using BSON2JSON and then using string\n",
    "functions or regular expressions to modify the data.\n",
    "\n",
    "The other option is to use the JSON_UPDATE statement. The\n",
    "syntax of the JSON_UPDATE function is:\n",
    "\n",
    "<pre>\n",
    "   JSON_UPDATE(document, '{$set: {field:value}}')\n",
    "   JSON_UPDATE(document, '{$unset: {field:null}}')\n",
    "</pre>\n",
    "  \n",
    "The arguments are: \n",
    "- *document* - BSON document \n",
    "- *field* - The field we are looking for \n",
    "- *value* - The value we want to set the field to\n",
    "\n",
    "There are three possible outcomes from using the JSON_UPDATE statement:\n",
    "- If the field is found, the existing value is replaced with the new one\n",
    "- If the field is not found, the field:value pair is added to the document\n",
    "- Using the $unset keyword and setting the value to the null will remove the field from the document\n",
    "\n",
    "The field can specify a portion of a structure, or an element of an\n",
    "array using the dot notation. The following SQL will\n",
    "illustrate how values can be added and removed from a document.\n",
    "\n",
    "A single record that contains 3 phone number extensions are\n",
    "added to a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM SANDBOX;\n",
    " \n",
    "INSERT INTO SANDBOX VALUES\n",
    "  JSON2BSON('{\"phone\":\"[1111,2222,3333]\"}');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To add a new field to the record, the JSON_UPDATE function needs to specify the \n",
    "field and value pair."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE SANDBOX\n",
    "  SET DATA = \n",
    "    JSON_UPDATE(DATA,'{ $set: {\"lastname\":\"HAAS\"}}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieving the document shows that the lastname field has now been added to the record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -j SELECT BSON2JSON(DATA) FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you specify a field that is an array type and do not\n",
    "specify an element, you will end up replacing the entire\n",
    "field with the value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -j\n",
    "UPDATE SANDBOX\n",
    "  SET DATA = \n",
    "    JSON_UPDATE(DATA,'{ $set: {\"phone\":\"9999\"}}');\n",
    "SELECT BSON2JSON(DATA) FROM SANDBOX;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -j SELECT BSON2JSON(DATA) FROM SANDBOX"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Running the SQL against the original phone data will work properly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -j\n",
    "UPDATE SANDBOX\n",
    "  SET DATA = \n",
    "    JSON_UPDATE(DATA,'{ $set: {\"phone.0\":9999}}');\n",
    "\n",
    "SELECT BSON2JSON(DATA) FROM SANDBOX;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To remove the phone number field you need to use the $unset keyword and set the field to null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -j\n",
    "UPDATE SANDBOX\n",
    "  SET DATA = \n",
    "    JSON_UPDATE(DATA,'{ $unset: {\"phone\":null}}');\n",
    "\n",
    "SELECT BSON2JSON(DATA) FROM SANDBOX;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='indexing'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Indexing JSON Documents \n",
    "\n",
    "Db2 supports computed indexes, which allows for the use\n",
    "of functions like JSON_VAL to be used as part of the index\n",
    "definition. For instance, searching for an employee number\n",
    "will result in a scan against the table if no indexes are\n",
    "defined:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP INDEX IX_JSON;\n",
    "SELECT JSON_VAL(EMP_DATA, 'lastname', 's:20') AS LASTNAME\n",
    "  FROM JSON_EMP\n",
    "WHERE  JSON_VAL(EMP_DATA, 'empno', 's:6') = '000010';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following command will time the select statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "noindex = %sql -t \\\n",
    "SELECT JSON_VAL(EMP_DATA, 'lastname', 's:20') AS LASTNAME \\\n",
    "  FROM JSON_EMP \\\n",
    "WHERE  JSON_VAL(EMP_DATA, 'empno', 's:6') = '000010'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create an index on the empno field, we use the JSON_VAL function to extract the \n",
    "empno from the JSON field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "CREATE INDEX IX_JSON ON JSON_EMP\n",
    "  (JSON_VAL(EMP_DATA,'empno','s:6'));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rerunning the SQL results in the following performance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "withindex = %%sql -t \\\n",
    "SELECT JSON_VAL(EMP_DATA, 'lastname', 's:20') AS LASTNAME \\\n",
    "  FROM JSON_EMP \\\n",
    "WHERE  JSON_VAL(EMP_DATA, 'empno', 's:6') = '000010' \\"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Db2 can now use the index to retrieve the record and the following plot shows the increased throughput."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -pb WITH RESULTS(RUN, RESULT) AS (VALUES ('No Index',{noindex}),('With Index',{withindex})) SELECT * FROM RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='simple'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simplifying JSON SQL Inserts and Retrieval\n",
    "\n",
    "From a development perspective, you always need to convert\n",
    "documents to and from JSON using the BSON2JSON and JSON2BSON\n",
    "functions. There are ways to hide these functions from an\n",
    "application and simplify some of the programming.\n",
    "\n",
    "One approach to simplifying the conversion of documents\n",
    "between formats is to use INSTEAD OF triggers. These\n",
    "triggers can intercept transactions before they are applied\n",
    "to the base tables. This approach requires that we create a\n",
    "view on top of an existing table.\n",
    "\n",
    "The first step is to create the base table with two copies\n",
    "of the JSON column. One will contain the original JSON\n",
    "character string while the second will contain the converted\n",
    "BSON. For this example, the JSON column will be called INFO,\n",
    "and the BSON column will be called BSONINFO. The use of two\n",
    "columns containing JSON would appear strange at first. The\n",
    "reason for the two columns is that Db2 expects the BLOB\n",
    "column to contain binary data. You cannot insert a character\n",
    "string (JSON) into the BSON column without converting it\n",
    "first. Db2 will raise an error so the JSON column is there\n",
    "to avoid an error while the conversion takes place.\n",
    "\n",
    "From a debugging perspective, we can keep both the CLOB and\n",
    "BLOB values in this table if we want. The trigger will set\n",
    "the JSON column to null after the BSON column has been\n",
    "populated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE BASE_EMP_TXS;\n",
    "\n",
    "CREATE TABLE BASE_EMP_TXS (\n",
    "  SEQNO    INT NOT NULL GENERATED ALWAYS AS IDENTITY,\n",
    "  INFO     VARCHAR(4000),\n",
    "  BSONINFO BLOB(4000) INLINE LENGTH 4000\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To use INSTEAD OF triggers, a view needs to be created on\n",
    "top of the base table. Note that we explicitly use the\n",
    "SYSTOOLS schema to make sure we are getting the correct\n",
    "function used here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE VIEW EMP_TXS AS\n",
    "  (SELECT SEQNO, BSON2JSON(BSONINFO) AS INFO FROM BASE_EMP_TXS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we can create three INSTEAD OF triggers to handle insert, \n",
    "updates and deletes on the view. \n",
    "\n",
    "On INSERT the DEFAULT keyword is used to generate the ID number, the JSON field is \n",
    "set to NULL and the BSON column contains the converted value of the JSON string. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -d\n",
    "CREATE OR REPLACE TRIGGER I_EMP_TXS\n",
    "  INSTEAD OF INSERT ON EMP_TXS\n",
    "  REFERENCING NEW AS NEW_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  INSERT INTO BASE_EMP_TXS VALUES (\n",
    "     DEFAULT,\n",
    "     NULL,\n",
    "     SYSTOOLS.JSON2BSON(NEW_TXS.INFO)\n",
    "     );\n",
    "END\n",
    "@"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On UPDATES, the sequence number remains the same, and the BSON field is updated \n",
    "with the contents of the JSON field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -d\n",
    "CREATE OR REPLACE TRIGGER U_EMP_TXS\n",
    "  INSTEAD OF UPDATE ON EMP_TXS\n",
    "  REFERENCING NEW AS NEW_TXS OLD AS OLD_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  UPDATE BASE_EMP_TXS \n",
    "     SET (INFO, BSONINFO) = (NULL,\n",
    "         SYSTOOLS.JSON2BSON(NEW_TXS.INFO)) \n",
    "     WHERE \n",
    "       BASE_EMP_TXS.SEQNO = OLD_TXS.SEQNO;\n",
    "END\n",
    "@"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, the DELETE trigger will just remove the row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -d\n",
    "CREATE OR REPLACE TRIGGER D_EMP_TX\n",
    "  INSTEAD OF DELETE ON EMP_TXS\n",
    "  REFERENCING OLD AS OLD_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  DELETE FROM BASE_EMP_TXS \n",
    "     WHERE \n",
    "       BASE_EMP_TXS.SEQNO = OLD_TXS.SEQNO;\n",
    "END\n",
    "@"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Applications will only deal with the EMP_TXS view. Any\n",
    "inserts will use the text version of the JSON and not have\n",
    "to worry about using the JSON2BSON function since the\n",
    "underlying INSTEAD OF trigger will take care of the\n",
    "conversion.\n",
    "\n",
    "The following insert statement only includes the JSON string\n",
    "since the sequence number will be generated automatically as\n",
    "part of the insert."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "  INSERT INTO EMP_TXS(INFO) VALUES (\n",
    "      '{\n",
    "      \"empno\":\"000010\",\n",
    "      \"firstnme\":\"CHRISTINE\",\n",
    "      \"midinit\":\"I\",\n",
    "      \"lastname\":\"HAAS\",\n",
    "      \"workdept\":\"A00\",\n",
    "      \"phoneno\":[3978],\n",
    "      \"hiredate\":\"01/01/1995\",\n",
    "      \"job\":\"PRES\",\n",
    "      \"edlevel\":18,\n",
    "      \"sex\":\"F\",\n",
    "      \"birthdate\":\"08/24/1963\",\n",
    "      \"pay\" : {\n",
    "        \"salary\":152750.00,\n",
    "        \"bonus\":1000.00,\n",
    "        \"comm\":4220.00}\n",
    "      }')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting from the EMP_TXS view will return the JSON in a readable format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -j SELECT INFO FROM EMP_TXS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The base table only contains the BSON but the view translates the value back into a readable format.\n",
    "\n",
    "An update statement that replaces the entire string works as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -j\n",
    "UPDATE EMP_TXS SET INFO = '{\"empno\":\"000010\"}' WHERE SEQNO = 1;\n",
    "\n",
    "SELECT INFO FROM EMP_TXS;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to manipulate the BSON directly (say change the employee number), \n",
    "you need to refer to the BASE table instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE BASE_EMP_TXS\n",
    "  SET BSONINFO = JSON_UPDATE(BSONINFO,\n",
    "    '{$set: {\"empno\":\"111111\"}}')\n",
    "  WHERE SEQNO = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can check it using our original view."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql -j SELECT INFO FROM EMP_TXS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Credits: IBM 2018, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "hide_code_all_hidden": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}