Get values from JSON using JSON_VALUE in SQL Server

Question

I have the below JSON (Sorry I dont know how to format it!) and I am struggling to understand how to extract values at the different levels.

My code so far is this which brings back the data required from the first few columns..

  ,JSON_VALUE(jsonstring,'$[0].addPoint') Addpoint
  ,JSON_VALUE(jsonstring,'$[0].department') department
  ,JSON_VALUE(jsonstring,'$[0].subBuilding') subBuilding
  ,JSON_VALUE(jsonstring,'$[0].buildingNumber') Buildingnumber
  ,JSON_VALUE(jsonstring,'$[0].buildingGroup') buildingGroup

However I am not sure how I would get the below columns..

"mpan" "serialnumber"

Can someone advise me as to what I am missing here? I haven't worked with JSON before and have googled but can't find a definite solution

[
   {
      "addPoint":null,
      "department":null,
      "subBuilding":null,
      "buildingNumber":"1",
      "buildingName":null,
      "buildingGroup":null,
      "poBox":null,
      "subStreet":"The Arches",
      "subLocality":null,
      "stateRegion":"Lancashire",
      "subAdministrativeArea":null,
      "administrativeArea":null,
      "superAdministrativeArea":null,
      "countryCode":"GBR",
      "countryName":null,
      "dpsZipPlus":"1B5",
      "formattedAddress":"TEST,,MANC,Lancashire,66666",
      "welshSubStreet":null,
      "welshStreet":null,
      "welshSubLocality":null,
      "welshLocality":null,
      "welshTown":null,
      "geographicInformation":null,
      "additionalItems":{
         "item":[
            {
               "key":"DATASOURCE",
               "value":"tu_REGISTER"
            }
         ],
         "tmp":null
      },
      "groupedAdditionalItems":null,
      "persons":null,
      "uprn":null,
      "lpi":null,
      "blpu":null,
      "streetDescriptor":null,
      "streetInformation":null,
      "companyInformation":null,
      "dnbCompanyInformation":null,
      "onsPointerInformation":null,
      "classification":null,
      "osAl2Toid":null,
      "osItnToid":null,
      "osTopoToid":null,
      "voaCtRecord":null,
      "voaNdrRecord":null,
      "apOSAPR":null,
      "rmUDPRN":"2744498",
      "mrOccCountSpecified":false,
      "alias":null,
      "utilitiesInformation":{
         "fuelType":1,
         "fuelTypeSpecified":true,
         "gasInformation":null,
         "electricityInformation":{
            "meterPoint":[
               {
                  "mpan":"162558070",
                  "meter":[
                     {
                        "serialNumber":"D07W05001",
                        "type":"N"
                     }
                  ],
                  "profileType":"02",
                  "timeSwitchCode":"811",
                  "lineLossFactorId":"531",
                  "standardSettlementConfiguration":"0151",
                  "energisationStatus":"E",
                  "energisationEffectiveFromDate":{
                     "day":5,
                     "daySpecified":true,
                     "month":12,
                     "monthSpecified":true,
                     "year":2014,
                     "yearSpecified":true
                  },
                  "distributorId":"16",
                  "gspid":"_G"
               }
            ],
            "tmp":null
         },
         "tmp":null
      },
      "organisation":null,
      "street":"Clive Street",
      "town":"MANCH",
      "postCode":"r4d 1ES",
      "locality":null
   }]

Answer 1

You can use the following paths:

$[0].utilitiesInformation.electricityInformation.meterPoint[0].mpan
$[0].utilitiesInformation.electricityInformation.meterPoint[0].meter[0].serialNumber