5-JSON.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a9825ce4",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b00c7cf",
   "metadata": {},
   "source": [
    "# Working with JSON Data\n",
    "\n",
    "Documentation reference link: [Using JSON Data](https://python-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4933ee95",
   "metadata": {},
   "outputs": [],
   "source": [
    "import oracledb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e091ca8",
   "metadata": {},
   "outputs": [],
   "source": [
    "un = \"pythondemo\"\n",
    "pw = \"welcome\"\n",
    "cs = \"localhost/orclpdb1\"\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a81ba26a",
   "metadata": {},
   "source": [
    "### JSON Storage:\n",
    "\n",
    "- Oracle Database 12c introduced JSON stored as a LOB or VARCHAR2\n",
    "\n",
    "- Oracle Database 21c a new optimized native binary format and a dedicated JSON type\n",
    "\n",
    "**Careful coding is required for apps that run in a mixed version environment**\n",
    "\n",
    "The first JSON example assumes you are Oracle Database 21c or later.\n",
    "\n",
    "Setup the schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caa04809",
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    try:\n",
    "        cursor.execute(\"drop table customers\")\n",
    "    except:\n",
    "        ;\n",
    "    cursor.execute(\"create table customers (k number, json_data json)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a84dfaeb",
   "metadata": {},
   "source": [
    "With Oracle Database 21c or later, you can bind Python objects directly to the JSON column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e14826f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "json_data = [\n",
    "    2.78,\n",
    "    True,\n",
    "    'Ocean Beach',\n",
    "    b'Some bytes',\n",
    "    {'keyA': 1, 'KeyB': 'Melbourne'},\n",
    "    datetime.date.today()\n",
    "]\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    cursor.setinputsizes(oracledb.DB_TYPE_JSON)\n",
    "    cursor.execute(\"insert into customers (k, json_data) values (1, :jbv)\", [json_data])\n",
    "    \n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c21d71e3",
   "metadata": {},
   "source": [
    "Querying returns the JSON in a familiar Python data structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d84a494d",
   "metadata": {},
   "outputs": [],
   "source": [
    "with connection.cursor() as cursor:\n",
    "    for row, in cursor.execute(\"select c.json_data from customers c where k = 1\"):\n",
    "        print(row)\n",
    "        \n",
    "# With Oracle Database 21c or later, this gives:\n",
    "# [Decimal('2.78'), True, 'Ocean Beach', b'Some bytes', {'keyA': Decimal('1'), 'KeyB': 'Melbourne'}, datetime.datetime(2022, 3, 4, 0, 0)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e18fc3b4",
   "metadata": {},
   "source": [
    "If you don't have a recent database, then you can still easily work with JSON.  Store it using BLOB and work with JSON strings. The Python \"json\" package can be used with many Python types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a54a7bca-c8d9-4f47-89a2-edaf6ada0a44",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    try:\n",
    "        cursor.execute(\"drop table customersblob\")\n",
    "    except:\n",
    "        ;\n",
    "    cursor.execute(\"\"\"create table customersblob (k number, \n",
    "                                                  json_data blob check (json_data is json)) \n",
    "                                                        lob (json_data) store as (cache)\"\"\")\n",
    " \n",
    "# INSERT\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    data = json_data = [\n",
    "        2.78,\n",
    "        True,\n",
    "        'Ocean Beach',\n",
    "        {'keyA': 1, 'KeyB': 'Melbourne'},\n",
    "    ]\n",
    "    cursor.execute(\"insert into customersblob (k, json_data) values (2, :jbv)\", [json.dumps(data)])\n",
    " \n",
    "# FETCH\n",
    "\n",
    "# Allow the BLOB column to be automatically recognized as storing JSON and not as storing arbitrary binary data.\n",
    "# Without this, you would need to use the Python json package on the returned row.\n",
    "oracledb.__future__.old_json_col_as_obj = True\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    for row, in cursor.execute(\"SELECT c.json_data FROM customersblob c where k = 2\"):\n",
    "        print(row)\n",
    "    \n",
    "connection.rollback()   "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}