TSQL Tuesday 87 - Example JSON Data

Example JSON data.sql

DECLARE @WebsiteJson nvarchar(max) = '{ "Users": [
  {
    "_id": "589f14e8427b0030d59615b1",
    "index": 0,
    "guid": "4a5ecd66-8c41-4553-9c1b-7597124b46e3",
    "isActive": true,
    "balance": "$1,418.71",
    "picture": "http://placehold.it/32x32",
    "age": 26,
    "eyeColor": "brown",
    "name": "Hanson Larson",
    "gender": "male",
    "company": "KENEGY",
    "email": "hansonlarson@kenegy.com",
    "phone": "+1 (804) 447-3852",
    "address": "698 Durland Place, Hachita, Louisiana, 1920",
    "about": "Consectetur laboris sunt proident ullamco ex excepteur duis cillum sit dolor occaecat officia. Sunt amet cupidatat enim mollit esse non minim dolore ullamco minim duis do. Sunt sint aliqua sit excepteur anim proident consequat magna reprehenderit laborum. Voluptate officia et duis sit laborum.\r\n",
    "registered": "2016-11-09T10:59:38 +05:00",
    "latitude": -72.754583,
    "longitude": 15.975401,
    "tags": [
      "culpa",
      "excepteur",
      "adipisicing",
      "reprehenderit",
      "eiusmod",
      "officia",
      "incididunt"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Blake Dodson"
      },
      {
        "id": 1,
        "name": "Marta Bullock"
      },
      {
        "id": 2,
        "name": "Benson Soto"
      }
    ],
    "greeting": "Hello, Hanson Larson! You have 7 unread messages.",
    "favoriteFruit": "apple"
  },
  {
    "_id": "589f14e8547a192d7a11a174",
    "index": 1,
    "guid": "a3c6533b-4c50-4054-9517-a03e8aa9c9ec",
    "isActive": true,
    "balance": "$3,450.53",
    "picture": "http://placehold.it/32x32",
    "age": 28,
    "eyeColor": "green",
    "name": "Wood Douglas",
    "gender": "male",
    "company": "FARMAGE",
    "email": "wooddouglas@farmage.com",
    "phone": "+1 (819) 515-3319",
    "address": "113 Dean Street, Kylertown, Massachusetts, 1666",
    "about": "Sunt laborum duis pariatur qui esse velit dolore mollit magna aute. Eiusmod velit dolor nostrud dolor culpa labore duis eiusmod sunt Lorem. Adipisicing eu minim voluptate veniam quis eu laboris dolore ipsum. Voluptate deserunt velit qui aliqua cillum. Voluptate in sit mollit irure velit fugiat dolore minim commodo. Commodo ea aliquip cupidatat commodo consequat ipsum laboris. Excepteur velit voluptate laborum dolor officia ullamco.\r\n",
    "registered": "2015-09-22T11:15:47 +04:00",
    "latitude": -88.972896,
    "longitude": 35.988156,
    "tags": [
      "tempor",
      "in",
      "adipisicing",
      "nostrud",
      "officia",
      "cupidatat",
      "occaecat"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Vivian Gates"
      },
      {
        "id": 1,
        "name": "Quinn Fitzgerald"
      },
      {
        "id": 2,
        "name": "Jenifer Lowery"
      }
    ],
    "greeting": "Hello, Wood Douglas! You have 2 unread messages.",
    "favoriteFruit": "banana"
  },
  {
    "_id": "589f14e8e9c6c96cd1f0b548",
    "index": 2,
    "guid": "4c71a47a-8212-46aa-97e3-bf8c136c49f5",
    "isActive": true,
    "balance": "$2,118.35",
    "picture": "http://placehold.it/32x32",
    "age": 27,
    "eyeColor": "green",
    "name": "Copeland Wiley",
    "gender": "male",
    "company": "OPTYK",
    "email": "copelandwiley@optyk.com",
    "phone": "+1 (801) 416-3699",
    "address": "824 Bristol Street, Sparkill, Delaware, 1706",
    "about": "In veniam elit nisi reprehenderit adipisicing veniam magna. Veniam proident commodo deserunt minim eiusmod commodo aliquip voluptate in occaecat id cillum fugiat. Excepteur magna adipisicing officia qui sit dolor nisi pariatur anim excepteur.\r\n",
    "registered": "2016-09-27T03:54:01 +04:00",
    "latitude": -52.752953,
    "longitude": -119.42916,
    "tags": [
      "excepteur",
      "labore",
      "irure",
      "voluptate",
      "enim",
      "commodo",
      "sint"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Harding Sanford"
      },
      {
        "id": 1,
        "name": "Tessa Colon"
      },
      {
        "id": 2,
        "name": "Washington Jordan"
      }
    ],
    "greeting": "Hello, Copeland Wiley! You have 7 unread messages.",
    "favoriteFruit": "strawberry"
  },
  {
    "_id": "589f14e857f47268893121d0",
    "index": 3,
    "guid": "1ba95a0c-7559-4fdd-b52a-94f5d2c76ca0",
    "isActive": true,
    "balance": "$1,955.54",
    "picture": "http://placehold.it/32x32",
    "age": 20,
    "eyeColor": "green",
    "name": "Kathleen Bradley",
    "gender": "female",
    "company": "PASTURIA",
    "email": "kathleenbradley@pasturia.com",
    "phone": "+1 (916) 463-3527",
    "address": "512 Holmes Lane, Chaparrito, District Of Columbia, 1651",
    "about": "Ea nostrud minim exercitation commodo laborum. Minim irure est sint adipisicing. Voluptate nisi fugiat qui et quis sint nisi ea deserunt laboris est dolor cillum. Ad sit duis nisi culpa aliquip incididunt.\r\n",
    "registered": "2015-07-20T03:11:35 +04:00",
    "latitude": -9.512905,
    "longitude": -108.671577,
    "tags": [
      "eiusmod",
      "enim",
      "tempor",
      "amet",
      "reprehenderit",
      "sit",
      "ut"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Rowe Crawford"
      },
      {
        "id": 1,
        "name": "Ginger Bray"
      },
      {
        "id": 2,
        "name": "Nichole Bradford"
      }
    ],
    "greeting": "Hello, Kathleen Bradley! You have 6 unread messages.",
    "favoriteFruit": "banana"
  },
  {
    "_id": "589f14e8128844a31ccb82e2",
    "index": 4,
    "guid": "03e2341e-da4d-48bb-8d6f-006234835828",
    "isActive": false,
    "balance": "$1,827.38",
    "picture": "http://placehold.it/32x32",
    "age": 32,
    "eyeColor": "brown",
    "name": "Colon Mays",
    "gender": "male",
    "company": "FREAKIN",
    "email": "colonmays@freakin.com",
    "phone": "+1 (919) 555-3410",
    "address": "627 Reed Street, Taft, Texas, 1724",
    "about": "Ullamco magna tempor dolore enim veniam aute incididunt. Deserunt nostrud fugiat reprehenderit consequat deserunt. Labore consequat magna pariatur amet mollit aute reprehenderit ea dolore exercitation anim.\r\n",
    "registered": "2014-06-08T02:13:34 +04:00",
    "latitude": 35.186997,
    "longitude": -1.722535,
    "tags": [
      "cillum",
      "amet",
      "ad",
      "enim",
      "tempor",
      "amet",
      "est"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Gladys Rojas"
      },
      {
        "id": 1,
        "name": "Luann Craft"
      },
      {
        "id": 2,
        "name": "Camille Gibson"
      }
    ],
    "greeting": "Hello, Colon Mays! You have 7 unread messages.",
    "favoriteFruit": "apple"
  }
]
}';