samples/03-json/04-index-json.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Index JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "7e048e8a-c2bf-4b7d-ad78-6dceb57d2b99"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's create a table where we can play with indexes. "
            ],
            "metadata": {
                "azdata_cell_guid": "a08807e0-7918-4209-bfad-669ffd042478"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "drop table if exists dbo.users_json_indexed;\r\n",
                "select id = identity(int, 1, 1), json_data into dbo.users_json_indexed from dbo.users_json order by id;\r\n",
                "alter table dbo.users_json_indexed add constraint pk__users_json_indexed primary key(id);\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "17880f6d-762d-4c23-afbe-e398c6390f69",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(4 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.023"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 51
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's also add some data into the created table, using existing `dbo.user_json` table"
            ],
            "metadata": {
                "azdata_cell_guid": "ebb07b01-35e0-4fc8-8028-0efb207c421d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "insert into dbo.users_json_indexed (json_data) \r\n",
                "select u.json_data from dbo.users_json u \r\n",
                "cross join sys.objects \r\n",
                "\r\n",
                "insert into dbo.users_json_indexed (json_data) \r\n",
                "values (N'{\"firstName\": \"Joe\", \"lastName\": \"Black\", \"age\": 42}')\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "30fb962b-031a-47c1-92f9-6323c5ea23b1"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(448 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.031"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 52
        },
        {
            "cell_type": "code",
            "source": [
                "select count(*) from dbo.users_json_indexed"
            ],
            "metadata": {
                "azdata_cell_guid": "2678be23-e192-4ad0-bcef-0d9928a3ace1"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 43,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "453"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th></tr><tr><td>453</td></tr></table>"
                    }
                }
            ],
            "execution_count": 43
        },
        {
            "cell_type": "markdown",
            "source": [
                "Expose the JSON elements we want index into columns"
            ],
            "metadata": {
                "azdata_cell_guid": "13c151e5-01df-42c2-acfd-03778d0677de"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "alter table dbo.users_json_indexed\r\n",
                "add FirstName as json_value(json_data, '$.firstName')\r\n",
                "go\r\n",
                "alter table dbo.users_json_indexed\r\n",
                "add LastName as json_value(json_data, '$.lastName')\r\n",
                "go\r\n",
                "alter table dbo.users_json_indexed\r\n",
                "add Age as json_value(json_data, '$.age')\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "bad74ae5-6d8b-4e57-9d53-3c731aa2b042"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.027"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 44
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's take a look at the table now. FirstName, LastName and Age are both accessible via JSON and via columns"
            ],
            "metadata": {
                "azdata_cell_guid": "89384324-0e19-49db-a075-1f4b8c621c69"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select * from dbo.users_json_indexed where id = 5"
            ],
            "metadata": {
                "azdata_cell_guid": "c655a67b-56bf-4bc1-a2d4-d0aee0052e36"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.019"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 45,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "json_data"
                                    },
                                    {
                                        "name": "FirstName"
                                    },
                                    {
                                        "name": "LastName"
                                    },
                                    {
                                        "name": "Age"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "5",
                                    "1": "{\r\n  \"firstName\": \"John\",\r\n  \"lastName\": \"Smith\",\r\n  \"isAlive\": true,\r\n  \"age\": 25,\r\n  \"address\": {\r\n    \"streetAddress\": \"21 2nd Street\",\r\n    \"city\": \"New York\",\r\n    \"state\": \"NY\",\r\n    \"postalCode\": \"10021-3100\"\r\n  },\r\n  \"phoneNumbers\": [\r\n    {\r\n      \"type\": \"home\",\r\n      \"number\": \"212 555-1234\"\r\n    },\r\n    {\r\n      \"type\": \"office\",\r\n      \"number\": \"646 555-4567\"\r\n    },\r\n    {\r\n      \"type\": \"mobile\",\r\n      \"number\": \"123 456-7890\"\r\n    }\r\n  ],\r\n  \"children\": [],\r\n  \"spouse\": null\r\n}",
                                    "2": "John",
                                    "3": "Smith",
                                    "4": "25"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>id</th><th>json_data</th><th>FirstName</th><th>LastName</th><th>Age</th></tr><tr><td>5</td><td>{\r\n  &quot;firstName&quot;: &quot;John&quot;,\r\n  &quot;lastName&quot;: &quot;Smith&quot;,\r\n  &quot;isAlive&quot;: true,\r\n  &quot;age&quot;: 25,\r\n  &quot;address&quot;: {\r\n    &quot;streetAddress&quot;: &quot;21 2nd Street&quot;,\r\n    &quot;city&quot;: &quot;New York&quot;,\r\n    &quot;state&quot;: &quot;NY&quot;,\r\n    &quot;postalCode&quot;: &quot;10021-3100&quot;\r\n  },\r\n  &quot;phoneNumbers&quot;: [\r\n    {\r\n      &quot;type&quot;: &quot;home&quot;,\r\n      &quot;number&quot;: &quot;212 555-1234&quot;\r\n    },\r\n    {\r\n      &quot;type&quot;: &quot;office&quot;,\r\n      &quot;number&quot;: &quot;646 555-4567&quot;\r\n    },\r\n    {\r\n      &quot;type&quot;: &quot;mobile&quot;,\r\n      &quot;number&quot;: &quot;123 456-7890&quot;\r\n    }\r\n  ],\r\n  &quot;children&quot;: [],\r\n  &quot;spouse&quot;: null\r\n}</td><td>John</td><td>Smith</td><td>25</td></tr></table>"
                    }
                }
            ],
            "execution_count": 45
        },
        {
            "cell_type": "markdown",
            "source": [
                "Run a query without index. It will require 41 IOPS to complete, as it needs to scan the whole table"
            ],
            "metadata": {
                "azdata_cell_guid": "40a3e0e2-85dd-49c2-b798-c7edebb28b4b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "set statistics io on\r\n",
                "select * from dbo.users_json_indexed where LastName = 'Black' \r\n",
                "set statistics io off\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "c94b15b2-47b6-4c18-8b1c-b4e197be3588",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's create an index on the column now..."
            ],
            "metadata": {
                "azdata_cell_guid": "bc6bf108-23ab-40f9-b7e4-ba06c09abb97"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "create nonclustered index ix1 on dbo.users_json_indexed(LastName)\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "40cab15b-fc60-4142-9476-d54b48c6ca83"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ix1' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 47
        },
        {
            "cell_type": "markdown",
            "source": [
                "...and now run the same query again. Only 4 IOPS! That's almost 10 times less than before!"
            ],
            "metadata": {
                "azdata_cell_guid": "25982560-11e7-4103-a080-cba9a9472439"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "set statistics io on\r\n",
                "select * from dbo.users_json_indexed where LastName = 'Black' \r\n",
                "set statistics io off\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "0e6668ab-50e9-4604-822e-66760ccf739d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Table 'users_json_indexed'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 48,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "json_data"
                                    },
                                    {
                                        "name": "FirstName"
                                    },
                                    {
                                        "name": "LastName"
                                    },
                                    {
                                        "name": "Age"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "453",
                                    "1": "{\"firstName\": \"Joe\", \"lastName\": \"Black\", \"age\": 42}",
                                    "2": "Joe",
                                    "3": "Black",
                                    "4": "42"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>id</th><th>json_data</th><th>FirstName</th><th>LastName</th><th>Age</th></tr><tr><td>453</td><td>{&quot;firstName&quot;: &quot;Joe&quot;, &quot;lastName&quot;: &quot;Black&quot;, &quot;age&quot;: 42}</td><td>Joe</td><td>Black</td><td>42</td></tr></table>"
                    }
                }
            ],
            "execution_count": 48
        },
        {
            "cell_type": "markdown",
            "source": [
                "The Azure SQL engine is smart enough to figure out that index can be used also if you access JSON directly:"
            ],
            "metadata": {
                "azdata_cell_guid": "04f87afc-bdb8-46a2-8b27-91aee3f828c4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "set statistics io on\r\n",
                "select * from dbo.users_json_indexed where json_value(json_data, '$.lastName') = 'Black' \r\n",
                "set statistics io off"
            ],
            "metadata": {
                "azdata_cell_guid": "55dc8399-32f4-45bf-b402-159fc7e0adc3"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Table 'users_json_indexed'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 50,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "json_data"
                                    },
                                    {
                                        "name": "FirstName"
                                    },
                                    {
                                        "name": "LastName"
                                    },
                                    {
                                        "name": "Age"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "453",
                                    "1": "{\"firstName\": \"Joe\", \"lastName\": \"Black\", \"age\": 42}",
                                    "2": "Joe",
                                    "3": "Black",
                                    "4": "42"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>id</th><th>json_data</th><th>FirstName</th><th>LastName</th><th>Age</th></tr><tr><td>453</td><td>{&quot;firstName&quot;: &quot;Joe&quot;, &quot;lastName&quot;: &quot;Black&quot;, &quot;age&quot;: 42}</td><td>Joe</td><td>Black</td><td>42</td></tr></table>"
                    }
                }
            ],
            "execution_count": 50
        }
    ]
}