0

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
   }]
3
  • If you're using SQL Server 2012, as you've stated, the above will error; JSON_VALUE was added in SQL Server 2017. Commented Aug 11, 2021 at 13:35
  • Sorry my mistake I have edited this now Commented Aug 11, 2021 at 13:36
  • So what RDBMS are you using then if not SQL Server? Commented Aug 11, 2021 at 13:36

1 Answer 1

1

You can use the following paths:

$[0].utilitiesInformation.electricityInformation.meterPoint[0].mpan
$[0].utilitiesInformation.electricityInformation.meterPoint[0].meter[0].serialNumber
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect..I understand it completely now! So simple. Thank you for this :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.