samples/03-json/03-create-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": [
                "# Create JSON"
            ],
            "metadata": {
                "azdata_cell_guid": "0fc3fbeb-2d0a-4e44-b1ce-178459e5eabe"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "[FOR JSON](https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server) allows you to turn a resultset into a _single_ JSON document:\r\n",
                "\r\n",
                "```json\r\n",
                "[\r\n",
                "\t{\r\n",
                "\t\t\"firstName\": \"John\",\r\n",
                "\t\t\"lastName\": \"Smith\",\r\n",
                "\t\t\"isAlive\": true,\r\n",
                "\t\t\"age\": 25,\r\n",
                "\t\t\"address\": [\r\n",
                "\t\t\t{\r\n",
                "\t\t\t\t\"streetAddress\": \"21 2nd Street\",\r\n",
                "\t\t\t\t\"city\": \"New York\",\r\n",
                "\t\t\t\t\"state\": \"NY\",\r\n",
                "\t\t\t\t\"postalCode\": \"10021-3100\"\r\n",
                "\t\t\t}\r\n",
                "\t\t]\r\n",
                "\t}\r\n",
                "    ...\r\n",
                "]\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "fb99501e-35b9-474f-ae7e-a5fee1bbe0e6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select \r\n",
                "\tu.firstName,\r\n",
                "\tu.lastName,\r\n",
                "\tu.isAlive,\r\n",
                "\tu.age,\r\n",
                "\t[address].streetAddress,\r\n",
                "\t[address].city,\r\n",
                "\t[address].[state],\r\n",
                "\t[address].postalCode\r\n",
                "from \r\n",
                "\tdbo.users u \r\n",
                "inner join \r\n",
                "\tdbo.user_addresses as [address] on u.id = [address].[user_id] \r\n",
                "for \r\n",
                "\tjson auto\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "f7961fcf-2908-48c3-91de-c45bcbacbc6b"
            },
            "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.014"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 1,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "JSON_F52E2B61-18A1-11d1-B105-00805F49916B"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "[{\"firstName\":\"John\",\"lastName\":\"Smith\",\"isAlive\":true,\"age\":25,\"address\":[{\"streetAddress\":\"21 2nd Street\",\"city\":\"New York\",\"state\":\"NY\",\"postalCode\":\"10021-3100\"}]},{\"firstName\":\"Maggie\",\"lastName\":\"Doe\",\"isAlive\":true,\"age\":32,\"address\":[{\"streetAddress\":\"109 1st Avenue\",\"city\":\"London\",\"postalCode\":\"SW1A0AA\"}]}]"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>JSON_F52E2B61-18A1-11d1-B105-00805F49916B</th></tr><tr><td>[{&quot;firstName&quot;:&quot;John&quot;,&quot;lastName&quot;:&quot;Smith&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:25,&quot;address&quot;:[{&quot;streetAddress&quot;:&quot;21 2nd Street&quot;,&quot;city&quot;:&quot;New York&quot;,&quot;state&quot;:&quot;NY&quot;,&quot;postalCode&quot;:&quot;10021-3100&quot;}]},{&quot;firstName&quot;:&quot;Maggie&quot;,&quot;lastName&quot;:&quot;Doe&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:32,&quot;address&quot;:[{&quot;streetAddress&quot;:&quot;109 1st Avenue&quot;,&quot;city&quot;:&quot;London&quot;,&quot;postalCode&quot;:&quot;SW1A0AA&quot;}]}]</td></tr></table>"
                    }
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "With `JSON_QUERY` you can better decide how the nested JSON object is created:\r\n",
                "```json\r\n",
                "[\r\n",
                "\t{\r\n",
                "\t\t\"firstName\": \"John\",\r\n",
                "\t\t\"lastName\": \"Smith\",\r\n",
                "\t\t\"isAlive\": true,\r\n",
                "\t\t\"age\": 25,\r\n",
                "\t\t\"address\": {\r\n",
                "\t\t\t\"streetAddress\": \"21 2nd Street\",\r\n",
                "\t\t\t\"city\": \"New York\",\r\n",
                "\t\t\t\"state\": \"NY\",\r\n",
                "\t\t\t\"postalCode\": \"10021-3100\"\r\n",
                "\t\t}\r\n",
                "    }\r\n",
                "    ...\r\n",
                "]\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "c9392b5d-91a7-4206-9e65-c8231a6bbfdc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select \r\n",
                "\tu.firstName,\r\n",
                "\tu.lastName,\r\n",
                "\tu.isAlive,\r\n",
                "\tu.age,\r\n",
                "    json_query((select streetAddress, city, [state], postalCode from dbo.user_addresses ua where ua.[user_id] = u.id for json auto, without_array_wrapper)) as [address]\r\n",
                "from \r\n",
                "\tdbo.users u \r\n",
                "for \r\n",
                "\tjson auto\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "2d8843a7-be07-4086-9aa7-ec817ccc81e4",
                "tags": []
            },
            "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.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 8,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "JSON_F52E2B61-18A1-11d1-B105-00805F49916B"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "[{\"firstName\":\"John\",\"lastName\":\"Smith\",\"isAlive\":true,\"age\":25,\"address\":{\"streetAddress\":\"21 2nd Street\",\"city\":\"New York\",\"state\":\"NY\",\"postalCode\":\"10021-3100\"}},{\"firstName\":\"Maggie\",\"lastName\":\"Doe\",\"isAlive\":true,\"age\":32,\"address\":{\"streetAddress\":\"109 1st Avenue\",\"city\":\"London\",\"postalCode\":\"SW1A0AA\"}}]"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>JSON_F52E2B61-18A1-11d1-B105-00805F49916B</th></tr><tr><td>[{&quot;firstName&quot;:&quot;John&quot;,&quot;lastName&quot;:&quot;Smith&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:25,&quot;address&quot;:{&quot;streetAddress&quot;:&quot;21 2nd Street&quot;,&quot;city&quot;:&quot;New York&quot;,&quot;state&quot;:&quot;NY&quot;,&quot;postalCode&quot;:&quot;10021-3100&quot;}},{&quot;firstName&quot;:&quot;Maggie&quot;,&quot;lastName&quot;:&quot;Doe&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:32,&quot;address&quot;:{&quot;streetAddress&quot;:&quot;109 1st Avenue&quot;,&quot;city&quot;:&quot;London&quot;,&quot;postalCode&quot;:&quot;SW1A0AA&quot;}}]</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you need even more control on how JSON is generated, you can use `FOR JSON PATH`. Columns alias will be used to create nested objects. For example: \r\n",
                "```json\r\n",
                "[\r\n",
                "\t{\r\n",
                "\t\t\"firstName\": \"John\",\r\n",
                "\t\t\"lastName\": \"Smith\",\r\n",
                "\t\t\"additionalInfo\": {\r\n",
                "\t\t\t\"isAlive\": true,\r\n",
                "\t\t\t\"age\": 25\r\n",
                "\t\t}\r\n",
                "\t},\r\n",
                "\t...\r\n",
                "]\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "6aa2e2f3-c669-42b4-a3bd-036a782eee63"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select \n",
                "\tu.firstName,\n",
                "\tu.lastName,    \n",
                "\tu.isAlive as 'additionalInfo.isAlive',\n",
                "\tu.age as 'additionalInfo.age'\n",
                "from \n",
                "\tdbo.users u \n",
                "for \n",
                "\tjson path\n",
                "go\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "eae056a8-dd45-4fc4-8404-ac2b5a7cf55e"
            },
            "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.018"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 4,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "JSON_F52E2B61-18A1-11d1-B105-00805F49916B"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "[{\"firstName\":\"John\",\"lastName\":\"Smith\",\"additionalInfo\":{\"isAlive\":true,\"age\":25}},{\"firstName\":\"Maggie\",\"lastName\":\"Doe\",\"additionalInfo\":{\"isAlive\":true,\"age\":32}}]"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>JSON_F52E2B61-18A1-11d1-B105-00805F49916B</th></tr><tr><td>[{&quot;firstName&quot;:&quot;John&quot;,&quot;lastName&quot;:&quot;Smith&quot;,&quot;additionalInfo&quot;:{&quot;isAlive&quot;:true,&quot;age&quot;:25}},{&quot;firstName&quot;:&quot;Maggie&quot;,&quot;lastName&quot;:&quot;Doe&quot;,&quot;additionalInfo&quot;:{&quot;isAlive&quot;:true,&quot;age&quot;:32}}]</td></tr></table>"
                    }
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "## How to create one document per row?"
            ],
            "metadata": {
                "azdata_cell_guid": "ebd30188-be0a-4fc5-9c2b-aaaa037b6b2b"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you want to **create one document per row**, instead of having one big JSON document with everything, you can use `JSON_QUERY` again:"
            ],
            "metadata": {
                "azdata_cell_guid": "777d7174-73c2-4a0f-a619-a6348cd61e57"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select\r\n",
                "    u2.id,\r\n",
                "    json_query((\r\n",
                "        select \r\n",
                "        u.firstName,\r\n",
                "        u.lastName,\r\n",
                "        u.isAlive,\r\n",
                "        u.age,\r\n",
                "        json_query((select streetAddress, city, [state], postalCode from dbo.user_addresses ua where ua.[user_id] = u.id for json auto, without_array_wrapper)) as [address]\r\n",
                "        from \r\n",
                "            dbo.users u \r\n",
                "        where\r\n",
                "            u.id = u2.id\r\n",
                "        for \r\n",
                "            json auto\r\n",
                "        )) as json_data\r\n",
                "from\r\n",
                "    dbo.users u2"
            ],
            "metadata": {
                "azdata_cell_guid": "830be4d7-8bcb-4b5d-a039-e177b262f8c6"
            },
            "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.034"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "json_data"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "[{\"firstName\":\"John\",\"lastName\":\"Smith\",\"isAlive\":true,\"age\":25,\"address\":{\"streetAddress\":\"21 2nd Street\",\"city\":\"New York\",\"state\":\"NY\",\"postalCode\":\"10021-3100\"}}]"
                                },
                                {
                                    "0": "2",
                                    "1": "[{\"firstName\":\"Maggie\",\"lastName\":\"Doe\",\"isAlive\":true,\"age\":32,\"address\":{\"streetAddress\":\"109 1st Avenue\",\"city\":\"London\",\"postalCode\":\"SW1A0AA\"}}]"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>id</th><th>json_data</th></tr><tr><td>1</td><td>[{&quot;firstName&quot;:&quot;John&quot;,&quot;lastName&quot;:&quot;Smith&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:25,&quot;address&quot;:{&quot;streetAddress&quot;:&quot;21 2nd Street&quot;,&quot;city&quot;:&quot;New York&quot;,&quot;state&quot;:&quot;NY&quot;,&quot;postalCode&quot;:&quot;10021-3100&quot;}}]</td></tr><tr><td>2</td><td>[{&quot;firstName&quot;:&quot;Maggie&quot;,&quot;lastName&quot;:&quot;Doe&quot;,&quot;isAlive&quot;:true,&quot;age&quot;:32,&quot;address&quot;:{&quot;streetAddress&quot;:&quot;109 1st Avenue&quot;,&quot;city&quot;:&quot;London&quot;,&quot;postalCode&quot;:&quot;SW1A0AA&quot;}}]</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        }
    ]
}