如何编写JSONPath语法使结果返回单个值而非数组（ADF适用）

问题描述 #

我是JSONPath新手，需要编写语法仅在满足特定条件时提取属性值，目标值并非数组元素。当前使用的语法返回数组格式，无法在Azure Data Factory（ADF）映射中正常工作，请求返回单个值的可行语法。

给定JSON数据 #

{"batchId":279,"companyId":"40","period":202208,"taxCode":"1","taxSystem":"","transactionDate":"2022-08-05T00:00:00.000","transactionNumber":222006089,"transactionType":"IF","year":2022,"accountingInformation":{"account":"4010","column1":{"attributeId":"H9","dimValue":"76"},"column2":{"attributeId":"B0","dimValue":"2170103"},"column3":{"attributeId":"","dimValue":""},"column4":{"attributeId":"BF","dimValue":"217010330"},"column5":{"attributeId":"10","dimValue":"3101"},"column6":{"attributeId":"06","dimValue":""},"column7":{"attributeId":"19","dimValue":"K"}},"categories":{"cat1":"H9","cat2":"B0","cat3":"","cat4":"BF","cat5":"10","cat6":"06","cat7":"19","dim1":"76","dim2":"2170103","dim3":"","dim4":"217010330","dim5":"3101","dim6":"","dim7":"K"},"amounts":{"amount":48.24,"amount3":0.0,"amount4":0.0,"currencyAmount":48.24,"currencyCode":"NOK","debitCreditFlag":1},"invoice":{"customerOrSupplierId":"58118","description":"","externalArchiveReference":"","externalReference":"2170103","invoiceNumber":"220238522","ledgerType":"P"},"additionalInformation":{"number":0,"orderLineNumber":0,"orderNumber":0,"sequenceNumber":1,"status":"","value":0.0,"valueDate":"2022-08-05T00:00:00.000"},"lastUpdated":{"updatedAt":"2022-09-05T10:59:11.633","updatedBy":"HELVES"}}

当前使用的JSONPath语法 #

$['accountingInformation']['column2'][?(@.attributeId=='B0')].dimValue

当前返回结果（数组格式） #

[
   "2170103"
]

解决方案 #

要返回单个值而非数组，有两种可行的语法：

方法1：提取数组第一个元素 #

在原路径末尾添加[0]，直接取数组的第一个元素：

$['accountingInformation']['column2'][?(@.attributeId=='B0')].dimValue[0]

返回结果为："2170103"

方法2：简化条件判断路径 #

由于column2本身是单个对象（非数组），可以直接通过条件判断后取值，若确定仅需匹配attributeId=='B0'的情况，也可使用更简洁的写法：

$['accountingInformation']['column2'][?(@.attributeId=='B0')].dimValue

再通过[0]提取单个值（原理同方法1）。

可行性说明 #

上述语法在Azure Data Factory的JSONPath实现中是支持的，能够返回单个字符串值，可正常用于映射场景。

内容的提问来源于stack exchange，提问作者Dan André Nyländer