sql - 在 SQL Server 中使用 JSON_VALUE 从 JSON 中获取值
问题描述
我有下面的 JSON(对不起,我不知道如何格式化它!)我正在努力理解如何在不同级别提取值。
到目前为止,我的代码是这样的,它带回了前几列所需的数据。
,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
但是我不确定如何获得以下列..
“mpan” “序列号”
有人可以告诉我我在这里缺少什么吗?我以前没有使用过 JSON 并用谷歌搜索但找不到明确的解决方案
[
{
"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
}]
解决方案
您可以使用以下路径:
$[0].utilitiesInformation.electricityInformation.meterPoint[0].mpan
$[0].utilitiesInformation.electricityInformation.meterPoint[0].meter[0].serialNumber
推荐阅读
- sql - 在选择语句中添加
- error-handling - errorFormatter koa-async-validator
- batch-file - 使用 7 zip 的批处理脚本
- java - 如何阻止对象离开屏幕?
- html - 将带有操作的 HTML 表单转换为带有逻辑的 React 表单提交
- jquery - jQuery自动完成动态更改源,没有if else
- ionic-framework - 在离子中使用本地存储时出现问题
- python - 创建 M 列的列表
- c# - 如何在 EPiServer CMS 编辑视图中设置选定选项卡?
- reactjs - 在哪里放置 proptypes 验证?