{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fa753135",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/run-llama/llama_index/blob/main/docs/examples/query_engine/json_query_engine.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e45f9b60-cd6b-4c15-958f-1feca5438128",
   "metadata": {},
   "source": [
    "# JSON Query Engine\n",
    "The JSON query engine is useful for querying JSON documents that conform to a JSON schema.\n",
    "\n",
    "This JSON schema is then used in the context of a prompt to convert a natural language query into a structured JSON Path query. This JSON Path query is then used to retrieve data to answer the given question."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c9ba47e",
   "metadata": {},
   "source": [
    "If you're opening this Notebook on colab, you will probably need to install LlamaIndex 🦙."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6efe6c2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install llama-index-llms-openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "485b8f71",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install llama-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7c5da2e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: jsonpath-ng in /Users/loganmarkewich/llama_index/llama-index/lib/python3.9/site-packages (1.5.3)\n",
      "Requirement already satisfied: ply in /Users/loganmarkewich/llama_index/llama-index/lib/python3.9/site-packages (from jsonpath-ng) (3.11)\n",
      "Requirement already satisfied: six in /Users/loganmarkewich/llama_index/llama-index/lib/python3.9/site-packages (from jsonpath-ng) (1.16.0)\n",
      "Requirement already satisfied: decorator in /Users/loganmarkewich/llama_index/llama-index/lib/python3.9/site-packages (from jsonpath-ng) (5.1.1)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 23.2.1 is available.\n",
      "You should consider upgrading via the '/Users/loganmarkewich/llama_index/llama-index/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "# First, install the jsonpath-ng package which is used by default to parse & execute the JSONPath queries.\n",
    "!pip install jsonpath-ng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "119eb42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging\n",
    "import sys\n",
    "\n",
    "logging.basicConfig(stream=sys.stdout, level=logging.INFO)\n",
    "logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7aa21e46",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import openai\n",
    "\n",
    "os.environ[\"OPENAI_API_KEY\"] = \"YOUR_KEY_HERE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "107396a9-4aa7-49b3-9f0f-a755726c19ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import Markdown, display"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5ece7d73-0f67-4ff5-95e5-249a25bd118c",
   "metadata": {},
   "source": [
    "### Let's start on a Toy JSON\n",
    "\n",
    "Very simple JSON object containing data from a blog post site with user comments.\n",
    "\n",
    "We will also provide a JSON schema (which we were able to generate by giving ChatGPT a sample of the JSON).\n",
    "\n",
    "#### Advice\n",
    "Do make sure that you've provided a helpful `\"description\"` value for each of the fields in your JSON schema.\n",
    "\n",
    "As you can see in the given example, the description for the `\"username\"` field mentions that usernames are lowercased. You'll see that this ends up being helpful for the LLM in producing the correct JSON path query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1484fe58-4853-4a76-bffc-435a9cce3e2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test on some sample data\n",
    "json_value = {\n",
    "    \"blogPosts\": [\n",
    "        {\n",
    "            \"id\": 1,\n",
    "            \"title\": \"First blog post\",\n",
    "            \"content\": \"This is my first blog post\",\n",
    "        },\n",
    "        {\n",
    "            \"id\": 2,\n",
    "            \"title\": \"Second blog post\",\n",
    "            \"content\": \"This is my second blog post\",\n",
    "        },\n",
    "    ],\n",
    "    \"comments\": [\n",
    "        {\n",
    "            \"id\": 1,\n",
    "            \"content\": \"Nice post!\",\n",
    "            \"username\": \"jerry\",\n",
    "            \"blogPostId\": 1,\n",
    "        },\n",
    "        {\n",
    "            \"id\": 2,\n",
    "            \"content\": \"Interesting thoughts\",\n",
    "            \"username\": \"simon\",\n",
    "            \"blogPostId\": 2,\n",
    "        },\n",
    "        {\n",
    "            \"id\": 3,\n",
    "            \"content\": \"Loved reading this!\",\n",
    "            \"username\": \"simon\",\n",
    "            \"blogPostId\": 2,\n",
    "        },\n",
    "    ],\n",
    "}\n",
    "\n",
    "# JSON Schema object that the above JSON value conforms to\n",
    "json_schema = {\n",
    "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
    "    \"description\": \"Schema for a very simple blog post app\",\n",
    "    \"type\": \"object\",\n",
    "    \"properties\": {\n",
    "        \"blogPosts\": {\n",
    "            \"description\": \"List of blog posts\",\n",
    "            \"type\": \"array\",\n",
    "            \"items\": {\n",
    "                \"type\": \"object\",\n",
    "                \"properties\": {\n",
    "                    \"id\": {\n",
    "                        \"description\": \"Unique identifier for the blog post\",\n",
    "                        \"type\": \"integer\",\n",
    "                    },\n",
    "                    \"title\": {\n",
    "                        \"description\": \"Title of the blog post\",\n",
    "                        \"type\": \"string\",\n",
    "                    },\n",
    "                    \"content\": {\n",
    "                        \"description\": \"Content of the blog post\",\n",
    "                        \"type\": \"string\",\n",
    "                    },\n",
    "                },\n",
    "                \"required\": [\"id\", \"title\", \"content\"],\n",
    "            },\n",
    "        },\n",
    "        \"comments\": {\n",
    "            \"description\": \"List of comments on blog posts\",\n",
    "            \"type\": \"array\",\n",
    "            \"items\": {\n",
    "                \"type\": \"object\",\n",
    "                \"properties\": {\n",
    "                    \"id\": {\n",
    "                        \"description\": \"Unique identifier for the comment\",\n",
    "                        \"type\": \"integer\",\n",
    "                    },\n",
    "                    \"content\": {\n",
    "                        \"description\": \"Content of the comment\",\n",
    "                        \"type\": \"string\",\n",
    "                    },\n",
    "                    \"username\": {\n",
    "                        \"description\": (\n",
    "                            \"Username of the commenter (lowercased)\"\n",
    "                        ),\n",
    "                        \"type\": \"string\",\n",
    "                    },\n",
    "                    \"blogPostId\": {\n",
    "                        \"description\": (\n",
    "                            \"Identifier for the blog post to which the comment\"\n",
    "                            \" belongs\"\n",
    "                        ),\n",
    "                        \"type\": \"integer\",\n",
    "                    },\n",
    "                },\n",
    "                \"required\": [\"id\", \"content\", \"username\", \"blogPostId\"],\n",
    "            },\n",
    "        },\n",
    "    },\n",
    "    \"required\": [\"blogPosts\", \"comments\"],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fea2edb-b3d4-4313-a656-d6edb00d93c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.llms.openai import OpenAI\n",
    "from llama_index.core.indices.struct_store import JSONQueryEngine\n",
    "\n",
    "llm = OpenAI(model=\"gpt-4\")\n",
    "\n",
    "nl_query_engine = JSONQueryEngine(\n",
    "    json_value=json_value,\n",
    "    json_schema=json_schema,\n",
    "    llm=llm,\n",
    ")\n",
    "raw_query_engine = JSONQueryEngine(\n",
    "    json_value=json_value,\n",
    "    json_schema=json_schema,\n",
    "    llm=llm,\n",
    "    synthesize_response=False,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "451836bc-b073-4838-8ab8-3def7d2c4d9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "nl_response = nl_query_engine.query(\n",
    "    \"What comments has Jerry been writing?\",\n",
    ")\n",
    "raw_response = raw_query_engine.query(\n",
    "    \"What comments has Jerry been writing?\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4253d4c3-f3e5-4779-bcd1-2e6e2818305f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "<h1>Natural language Response</h1><br><b>Jerry has written the comment \"Nice post!\".</b>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<h1>Raw JSON Response</h1><br><b>[\"Nice post!\"]</b>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(\n",
    "    Markdown(f\"<h1>Natural language Response</h1><br><b>{nl_response}</b>\")\n",
    ")\n",
    "display(Markdown(f\"<h1>Raw JSON Response</h1><br><b>{raw_response}</b>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e10b7da-b355-49b2-9f80-f17541d4f850",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "$.comments[?(@.username=='jerry')].content\n"
     ]
    }
   ],
   "source": [
    "# get the json path query string. Same would apply to raw_response\n",
    "print(nl_response.metadata[\"json_path_response_str\"])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}