samples/03-json/01-query-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": [
                "# Extract scalar values from JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "5c5a95a1-c3e2-4d0b-b140-42004964bd00"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "To extract a scalar value from a JSON document, the function [JSON_VALUE](https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql) is available"
            ],
            "metadata": {
                "azdata_cell_guid": "176a15d0-02bc-4e07-98e4-ecfe12f9bba1"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "declare @json varchar(max)\r\n",
                "select top (1) @json = json_data from dbo.users_json where id = 1\r\n",
                "select json_value(@json, '$.firstName')\t"
            ],
            "metadata": {
                "azdata_cell_guid": "9dbe910d-c4bd-47ef-83d1-24a07f5fb037",
                "tags": []
            },
            "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.016"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 10,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "John"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th></tr><tr><td>John</td></tr></table>"
                    }
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Extract non-scalar values from JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "74eae62d-e71d-4e3b-b0c1-969878f42cc1"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "[JSON_QUERY](https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql) instead allows objects or arrays to be returned"
            ],
            "metadata": {
                "azdata_cell_guid": "284fc1a6-9b0e-4a67-97a9-787ccb9814a4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "declare @json varchar(max)\r\n",
                "select top (1) @json = json_data from dbo.users_json where id = 1\r\n",
                "select json_query(@json, '$.address') \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "fdcef8db-3b06-4725-8fe2-d4bea6073072"
            },
            "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.015"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "{\r\n    \"streetAddress\": \"21 2nd Street\",\r\n    \"city\": \"New York\",\r\n    \"state\": \"NY\",\r\n    \"postalCode\": \"10021-3100\"\r\n  }"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th></tr><tr><td>{\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  }</td></tr></table>"
                    }
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Extract Key-Value pairs from JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "b5a6a00b-008b-45c3-a5b9-3339fb5f0f2c"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "With [OPENJSON](https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql) is possible to operate on JSON in more powerful and flexible ways. The _cross apply_ operator can be thought as a _for each_ operator. Used togheter with `OPENJSON` it enables you to easily operate on all JSON stored into a table column just using a declarative approach. The following code, for example, will extract all key-value pairs from each JSON stored in the `json_data` column of the `dbo.users_json` table."
            ],
            "metadata": {
                "azdata_cell_guid": "b4692d80-5dc2-469f-9a8b-ee1ddec05833"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select\r\n",
                "    j.id as document_id,\r\n",
                "    t.*\r\n",
                "from\r\n",
                "    dbo.users_json as j\r\n",
                "cross apply\r\n",
                "    openjson(j.json_data) as t"
            ],
            "metadata": {
                "azdata_cell_guid": "498c8f54-e519-4e28-aa99-1b1bb5b07993",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(18 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.011"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "document_id"
                                    },
                                    {
                                        "name": "key"
                                    },
                                    {
                                        "name": "value"
                                    },
                                    {
                                        "name": "type"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "firstName",
                                    "2": "John",
                                    "3": "1"
                                },
                                {
                                    "0": "1",
                                    "1": "lastName",
                                    "2": "Smith",
                                    "3": "1"
                                },
                                {
                                    "0": "1",
                                    "1": "isAlive",
                                    "2": "true",
                                    "3": "3"
                                },
                                {
                                    "0": "1",
                                    "1": "age",
                                    "2": "25",
                                    "3": "2"
                                },
                                {
                                    "0": "1",
                                    "1": "address",
                                    "2": "{\r\n    \"streetAddress\": \"21 2nd Street\",\r\n    \"city\": \"New York\",\r\n    \"state\": \"NY\",\r\n    \"postalCode\": \"10021-3100\"\r\n  }",
                                    "3": "5"
                                },
                                {
                                    "0": "1",
                                    "1": "phoneNumbers",
                                    "2": "[\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  ]",
                                    "3": "4"
                                },
                                {
                                    "0": "1",
                                    "1": "children",
                                    "2": "[]",
                                    "3": "4"
                                },
                                {
                                    "0": "1",
                                    "1": "spouse",
                                    "2": "NULL",
                                    "3": "0"
                                },
                                {
                                    "0": "2",
                                    "1": "firstName",
                                    "2": "Maggie",
                                    "3": "1"
                                },
                                {
                                    "0": "2",
                                    "1": "lastName",
                                    "2": "Doe",
                                    "3": "1"
                                },
                                {
                                    "0": "2",
                                    "1": "isAlive",
                                    "2": "true",
                                    "3": "3"
                                },
                                {
                                    "0": "2",
                                    "1": "age",
                                    "2": "32",
                                    "3": "2"
                                },
                                {
                                    "0": "2",
                                    "1": "address",
                                    "2": "{\r\n    \"streetAddress\": \"109 1st Avenue\",\r\n    \"city\": \"London\",\r\n    \"postalCode\": \"SW1A0AA\"\r\n  }",
                                    "3": "5"
                                },
                                {
                                    "0": "2",
                                    "1": "phoneNumbers",
                                    "2": "[\r\n    {\r\n      \"type\": \"home\",\r\n      \"number\": \"020 7946 0891\"\r\n    },\r\n    {\r\n      \"type\": \"office\",\r\n      \"number\": \"020 7946 0986\"\r\n    },\r\n    {\r\n      \"type\": \"mobile\",\r\n      \"number\": \"123 789-4560\"\r\n    }\r\n  ]",
                                    "3": "4"
                                },
                                {
                                    "0": "2",
                                    "1": "children",
                                    "2": "[\"Annette\", \"Richard\"]",
                                    "3": "4"
                                },
                                {
                                    "0": "2",
                                    "1": "spouse",
                                    "2": "Andrew Callaghan",
                                    "3": "1"
                                },
                                {
                                    "0": "3",
                                    "1": "0",
                                    "2": "{\r\n  \"firstName\": \"Mark\",\r\n  \"lastName\": \"Brown\"\r\n}",
                                    "3": "5"
                                },
                                {
                                    "0": "3",
                                    "1": "1",
                                    "2": "{\r\n  \"firstName\": \"Mike\",\r\n  \"lastName\": \"Green\" \r\n}",
                                    "3": "5"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>document_id</th><th>key</th><th>value</th><th>type</th></tr><tr><td>1</td><td>firstName</td><td>John</td><td>1</td></tr><tr><td>1</td><td>lastName</td><td>Smith</td><td>1</td></tr><tr><td>1</td><td>isAlive</td><td>true</td><td>3</td></tr><tr><td>1</td><td>age</td><td>25</td><td>2</td></tr><tr><td>1</td><td>address</td><td>{\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  }</td><td>5</td></tr><tr><td>1</td><td>phoneNumbers</td><td>[\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  ]</td><td>4</td></tr><tr><td>1</td><td>children</td><td>[]</td><td>4</td></tr><tr><td>1</td><td>spouse</td><td>NULL</td><td>0</td></tr><tr><td>2</td><td>firstName</td><td>Maggie</td><td>1</td></tr><tr><td>2</td><td>lastName</td><td>Doe</td><td>1</td></tr><tr><td>2</td><td>isAlive</td><td>true</td><td>3</td></tr><tr><td>2</td><td>age</td><td>32</td><td>2</td></tr><tr><td>2</td><td>address</td><td>{\r\n    &quot;streetAddress&quot;: &quot;109 1st Avenue&quot;,\r\n    &quot;city&quot;: &quot;London&quot;,\r\n    &quot;postalCode&quot;: &quot;SW1A0AA&quot;\r\n  }</td><td>5</td></tr><tr><td>2</td><td>phoneNumbers</td><td>[\r\n    {\r\n      &quot;type&quot;: &quot;home&quot;,\r\n      &quot;number&quot;: &quot;020 7946 0891&quot;\r\n    },\r\n    {\r\n      &quot;type&quot;: &quot;office&quot;,\r\n      &quot;number&quot;: &quot;020 7946 0986&quot;\r\n    },\r\n    {\r\n      &quot;type&quot;: &quot;mobile&quot;,\r\n      &quot;number&quot;: &quot;123 789-4560&quot;\r\n    }\r\n  ]</td><td>4</td></tr><tr><td>2</td><td>children</td><td>[&quot;Annette&quot;, &quot;Richard&quot;]</td><td>4</td></tr><tr><td>2</td><td>spouse</td><td>Andrew Callaghan</td><td>1</td></tr><tr><td>3</td><td>0</td><td>{\r\n  &quot;firstName&quot;: &quot;Mark&quot;,\r\n  &quot;lastName&quot;: &quot;Brown&quot;\r\n}</td><td>5</td></tr><tr><td>3</td><td>1</td><td>{\r\n  &quot;firstName&quot;: &quot;Mike&quot;,\r\n  &quot;lastName&quot;: &quot;Green&quot; \r\n}</td><td>5</td></tr></table>"
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "In case of an array of scalar, for example `{ \"children\": [\"Annette\", \"Richard\"] }`, values will be returned as rows. This ability make really easy to pass arrays to Azure SQL from an external application and use them using regular `SELECT`/`INSERT`/`UPDATE`/`DELETE` commands"
            ],
            "metadata": {
                "azdata_cell_guid": "a449ef85-f69f-4f5c-bcee-5221bbeecc8f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select\r\n",
                "    c.[value]\r\n",
                "from\r\n",
                "    dbo.users_json as j\r\n",
                "cross apply\r\n",
                "    openjson(j.json_data, '$.children') as c"
            ],
            "metadata": {
                "azdata_cell_guid": "6cd80b1d-be50-4a6d-ab68-e56cae973795"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.015"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 13,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "value"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Annette"
                                },
                                {
                                    "0": "Richard"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>value</th></tr><tr><td>Annette</td></tr><tr><td>Richard</td></tr></table>"
                    }
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Apply Schema-On-Read to JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "1c4bce72-0706-4e68-80db-fe9b53546aeb"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Instead of getting key-value pairs, it is possible to apply a schema to JSON and get a table as result"
            ],
            "metadata": {
                "azdata_cell_guid": "2d2e22d1-77d0-4aec-9e33-95e19bc6c4f4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select\r\n",
                "    j.id as document_id,\r\n",
                "    t.*\r\n",
                "from\r\n",
                "    dbo.users_json as j\r\n",
                "cross apply\r\n",
                "    openjson(j.json_data) with\r\n",
                "    (\r\n",
                "        FirstName nvarchar(50) '$.firstName',\r\n",
                "        LastName nvarchar(50) '$.lastName',\r\n",
                "        Age int '$.age',\r\n",
                "        [State] nvarchar(50) '$.address.state'\r\n",
                "    ) as t"
            ],
            "metadata": {
                "azdata_cell_guid": "2e317d6c-823b-41c5-9a2c-84e19b0da919"
            },
            "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.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 14,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "document_id"
                                    },
                                    {
                                        "name": "FirstName"
                                    },
                                    {
                                        "name": "LastName"
                                    },
                                    {
                                        "name": "Age"
                                    },
                                    {
                                        "name": "State"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "John",
                                    "2": "Smith",
                                    "3": "25",
                                    "4": "NY"
                                },
                                {
                                    "0": "2",
                                    "1": "Maggie",
                                    "2": "Doe",
                                    "3": "32",
                                    "4": "NULL"
                                },
                                {
                                    "0": "3",
                                    "1": "Mark",
                                    "2": "Brown",
                                    "3": "NULL",
                                    "4": "NULL"
                                },
                                {
                                    "0": "3",
                                    "1": "Mike",
                                    "2": "Green",
                                    "3": "NULL",
                                    "4": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>document_id</th><th>FirstName</th><th>LastName</th><th>Age</th><th>State</th></tr><tr><td>1</td><td>John</td><td>Smith</td><td>25</td><td>NY</td></tr><tr><td>2</td><td>Maggie</td><td>Doe</td><td>32</td><td>NULL</td></tr><tr><td>3</td><td>Mark</td><td>Brown</td><td>NULL</td><td>NULL</td></tr><tr><td>3</td><td>Mike</td><td>Green</td><td>NULL</td><td>NULL</td></tr></table>"
                    }
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "If JSON has nested objects, it is possibile to use `OPENJSON` multiple times. In the following sample, `$.phoneNumbers` is passed as a JSON option to a nested `OPENJSON` that operates on it. This time the _outer apply_ is used instead of _cross apply_ as it will return rows even for those JSON documents where `phoneNumbers` element is missing. If you only want to get rows if `phoneNumbers` element exist then you can replace `OUTER APPLY` with `CROSS APPLY`"
            ],
            "metadata": {
                "azdata_cell_guid": "6646f6ef-0e60-42a8-af2a-9012b6dfe32c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select\r\n",
                "    j.id as document_id,\r\n",
                "    t.FirstName,    \r\n",
                "    t.LastName,\r\n",
                "    t.Age,\r\n",
                "    t.State,\r\n",
                "    pn.PhoneType,\r\n",
                "    pn.PhoneNumber\r\n",
                "from\r\n",
                "    dbo.users_json as j\r\n",
                "cross apply\r\n",
                "    openjson(j.json_data) with\r\n",
                "    (\r\n",
                "        FirstName nvarchar(50) '$.firstName',\r\n",
                "        LastName nvarchar(50) '$.lastName',\r\n",
                "        Age int '$.age',\r\n",
                "        [State] nvarchar(50) '$.address.state',    \r\n",
                "        PhoneNumbers nvarchar(max) '$.phoneNumbers' as json\r\n",
                "    ) as t\r\n",
                "outer apply\r\n",
                "    openjson(t.PhoneNumbers) with \r\n",
                "    (\r\n",
                "        PhoneType nvarchar(50) '$.type',\r\n",
                "        PhoneNumber nvarchar(50) '$.number'\r\n",
                "    ) as pn"
            ],
            "metadata": {
                "azdata_cell_guid": "238133c8-7d94-4b4b-94fe-576b81b75590"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(8 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 15,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "document_id"
                                    },
                                    {
                                        "name": "FirstName"
                                    },
                                    {
                                        "name": "LastName"
                                    },
                                    {
                                        "name": "Age"
                                    },
                                    {
                                        "name": "State"
                                    },
                                    {
                                        "name": "PhoneType"
                                    },
                                    {
                                        "name": "PhoneNumber"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "John",
                                    "2": "Smith",
                                    "3": "25",
                                    "4": "NY",
                                    "5": "home",
                                    "6": "212 555-1234"
                                },
                                {
                                    "0": "1",
                                    "1": "John",
                                    "2": "Smith",
                                    "3": "25",
                                    "4": "NY",
                                    "5": "office",
                                    "6": "646 555-4567"
                                },
                                {
                                    "0": "1",
                                    "1": "John",
                                    "2": "Smith",
                                    "3": "25",
                                    "4": "NY",
                                    "5": "mobile",
                                    "6": "123 456-7890"
                                },
                                {
                                    "0": "2",
                                    "1": "Maggie",
                                    "2": "Doe",
                                    "3": "32",
                                    "4": "NULL",
                                    "5": "home",
                                    "6": "020 7946 0891"
                                },
                                {
                                    "0": "2",
                                    "1": "Maggie",
                                    "2": "Doe",
                                    "3": "32",
                                    "4": "NULL",
                                    "5": "office",
                                    "6": "020 7946 0986"
                                },
                                {
                                    "0": "2",
                                    "1": "Maggie",
                                    "2": "Doe",
                                    "3": "32",
                                    "4": "NULL",
                                    "5": "mobile",
                                    "6": "123 789-4560"
                                },
                                {
                                    "0": "3",
                                    "1": "Mark",
                                    "2": "Brown",
                                    "3": "NULL",
                                    "4": "NULL",
                                    "5": "NULL",
                                    "6": "NULL"
                                },
                                {
                                    "0": "3",
                                    "1": "Mike",
                                    "2": "Green",
                                    "3": "NULL",
                                    "4": "NULL",
                                    "5": "NULL",
                                    "6": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>document_id</th><th>FirstName</th><th>LastName</th><th>Age</th><th>State</th><th>PhoneType</th><th>PhoneNumber</th></tr><tr><td>1</td><td>John</td><td>Smith</td><td>25</td><td>NY</td><td>home</td><td>212 555-1234</td></tr><tr><td>1</td><td>John</td><td>Smith</td><td>25</td><td>NY</td><td>office</td><td>646 555-4567</td></tr><tr><td>1</td><td>John</td><td>Smith</td><td>25</td><td>NY</td><td>mobile</td><td>123 456-7890</td></tr><tr><td>2</td><td>Maggie</td><td>Doe</td><td>32</td><td>NULL</td><td>home</td><td>020 7946 0891</td></tr><tr><td>2</td><td>Maggie</td><td>Doe</td><td>32</td><td>NULL</td><td>office</td><td>020 7946 0986</td></tr><tr><td>2</td><td>Maggie</td><td>Doe</td><td>32</td><td>NULL</td><td>mobile</td><td>123 789-4560</td></tr><tr><td>3</td><td>Mark</td><td>Brown</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td></tr><tr><td>3</td><td>Mike</td><td>Green</td><td>NULL</td><td>NULL</td><td>NULL</td><td>NULL</td></tr></table>"
                    }
                }
            ],
            "execution_count": 15
        }
    ]
}