如何用JMESPath提取JSON中全部accountId对应的#value值？

需要提取JSON数据中所有_name属性等于accountId的#value值，当前使用JMESPath的Python代码仅能捕获每个对象中的第一个符合条件的值，但部分对象存在多个_name为accountId的属性，无法获取全部对应值。

JSON示例 #

[{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_IIALFSG400_-820452415","identifier":[{"#value":"1006009","_name":"legalEntityId"},{"#value":"1006009","_name":"Fenergo:LegalEntityId"},{"#value":"INSIGHT IM GBL-INS AL-FSG00400","_name":"shortName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC FSG00400","_name":"longName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC FSG00400","_name":"bloombergCompanyName"},{"#value":"INSAGENT G","_name":"GROUPREF"},{"#value":"IINVGB21XXX","_name":"BIC"},{"#value":"CUS_IIALFSG400","_name":"STRATREF"},{"#value":"1548","_name":"CUSTREF"},{"#value":"IIALFSG400","_name":"accountId"},{"#value":"INSIGHT IM GBL-INS AL-FSG00400","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_IIALILFEUL_1642989696","identifier":[{"#value":"1006010","_name":"legalEntityId"},{"#value":"1006010","_name":"Fenergo:LegalEntityId"},{"#value":"INSIGHT IM GBL-INS AL-ILFEUL","_name":"shortName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC ILFEUL","_name":"longName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC ILFEUL","_name":"bloombergCompanyName"},{"#value":"INSAGENT G","_name":"GROUPREF"},{"#value":"IINVGB21XXX","_name":"BIC"},{"#value":"CUS_IIALILFEUL","_name":"STRATREF"},{"#value":"1529","_name":"CUSTREF"},{"#value":"IIALILFEUL","_name":"accountId"},{"#value":"INSIGHT IM GBL-INS AL-ILFEUL","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_IIALFSG372_1317864966","identifier":[{"#value":"1006011","_name":"legalEntityId"},{"#value":"1006011","_name":"Fenergo:LegalEntityId"},{"#value":"INSIGHT IM GBL-INS AL- FSG372","_name":"shortName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC FSG00372","_name":"longName"},{"#value":"INSIGHT INVESTMENT MANAGEMENT (GLOBAL) AC FSG00372","_name":"bloombergCompanyName"},{"#value":"INSAGENT G","_name":"GROUPREF"},{"#value":"IINVGB21XXX","_name":"BIC"},{"#value":"CUS_IIALFSG372","_name":"STRATREF"},{"#value":"1484","_name":"CUSTREF"},{"#value":"IIALFSG372","_name":"accountId"},{"#value":"INSIGHT IM GBL-INS AL- FSG372","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALP29M_1573801127","identifier":[{"#value":"1006012","_name":"legalEntityId"},{"#value":"1006012","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29M","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29M","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29M","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALP29M","_name":"STRATREF"},{"#value":"914","_name":"CUSTREF"},{"#value":"DBALP29M","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29M","_name":"accountName"},{"#value":"DBLP29MTRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T-ALP29M","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALALP034_-1801476458","identifier":[{"#value":"1006013","_name":"legalEntityId"},{"#value":"1006013","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP034","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP034","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP034","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALALP034","_name":"STRATREF"},{"#value":"968","_name":"CUSTREF"},{"#value":"DBLP034TRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T ALP034","_name":"accountName"},{"#value":"DBALALP034","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP034","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALP29R_-1958336502","identifier":[{"#value":"1006014","_name":"legalEntityId"},{"#value":"1006014","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29R","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29R","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29R","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALP29R","_name":"STRATREF"},{"#value":"929","_name":"CUSTREF"},{"#value":"DBLP29RTRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T-ALP29R","_name":"accountName"},{"#value":"DBALP29R","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29R","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALP29P_-2120542118","identifier":[{"#value":"1006015","_name":"legalEntityId"},{"#value":"1006015","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29P","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29P","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29P","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALP29P","_name":"STRATREF"},{"#value":"974","_name":"CUSTREF"},{"#value":"DBLP29PTRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T-ALP29P","_name":"accountName"},{"#value":"DBALP29P","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29P","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALALP032_-602699160","identifier":[{"#value":"1006016","_name":"legalEntityId"},{"#value":"1006016","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP032","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP032","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP032","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALALP032","_name":"STRATREF"},{"#value":"966","_name":"CUSTREF"},{"#value":"DBALALP032","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP032","_name":"accountName"},{"#value":"DBLP032TRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T ALP032","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALP29X_717263244","identifier":[{"#value":"1006017","_name":"legalEntityId"},{"#value":"1006017","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29X","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29X","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP29X","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALP29X","_name":"STRATREF"},{"#value":"930","_name":"CUSTREF"},{"#value":"DBLP29XTRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T-ALP29X","_name":"accountName"},{"#value":"DBALP29X","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP29X","_name":"accountName"}]},{"_href":"goldenSource-counterpartySrc_transformationFlow_CUS_DBALALP031_2013821822","identifier":[{"#value":"1006018","_name":"legalEntityId"},{"#value":"1006018","_name":"Fenergo:LegalEntityId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP031","_name":"shortName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP031","_name":"longName"},{"#value":"DEUTSCHE BANK AG (AS AGENT LENDER) AC ALP031","_name":"bloombergCompanyName"},{"#value":"DB AGENT G","_name":"GROUPREF"},{"#value":"DEUTGB2LASL","_name":"BIC"},{"#value":"CUS_DBALALP031","_name":"STRATREF"},{"#value":"964","_name":"CUSTREF"},{"#value":"DBLP031TRI","_name":"accountId"},{"#value":"DEUTS BK-LDN BR-DB AL-T ALP031","_name":"accountName"},{"#value":"DBALALP031","_name":"accountId"},{"#value":"DEUT BK-LDN BR-DB AL-ALP031","_name":"accountName"}]}]

现有Python代码 #

cpty_fenergo = []
date = "2022-10-07"

cdw_legalEntity = "http://svc-alt-uat-cdw/identifier/legalEntityClients/?yyyy-mm-dd=2022-10-07&limit=10"


download_file(
    cdw_legalEntity, countsCDWdatasimple(cdw_legalEntity), "cdw_legalEntity.csv"
)

f_cpty = (
    open(f"{tempdir}\\cdw_legalEntity.csv", encoding="cp437").read().replace("\n", "")
)
json_cpty = json.loads(f_cpty, strict=False)


for i, doc in enumerate(json_cpty):

    try:
        group_ref = jmespath.search(f"identifier[?_name == 'GROUPREF']", doc)
        [0]["#value"]
    except:
        group_ref = ""
    try:  # identifier
        cpty_id = jmespath.search(f"identifier[?_name == 'accountId']", doc,)
        [0]["#value"]
    except:
        cpty_id = ""
    try:
        legalEntityId = jmespath.search(f"identifier[?_name == 'legalEntityId']", doc,)
        [0]["#value"]
    except:
        legalEntityId = ""

    cpty_fenergo.append(cpty_id)
    # cpty_fenergo.append((legalEntityId, cpty_id, group_ref)) 

# make list unique
cpty_fnId = list(set(cpty_fenergo))


print(len(cpty_fnId))
print(cpty_fnId)

#output:
10
['IIALFSG372', 'IIALFSG400', 'DBLP034TRI', 'IIALILFEUL', 'DBLP29XTRI', 'DBLP29RTRI', 'DBLP29PTRI', 'DBALP29M', 'DBLP031TRI', 'DBALALP032']

解决方案 #

1. 优化JMESPath表达式 #

原表达式仅筛选出符合条件的identifier对象数组，要直接提取所有#value，使用JMESPath的投影语法：

"identifier[?_name == 'accountId'].#value"

该表达式会返回所有_name为accountId的对象的#value组成的列表，无需手动取第一个元素。

2. 修改代码逻辑 #

遍历每个JSON对象时，获取所有符合条件的accountId值，并用extend将这些值添加到结果列表中，而不是append整个列表。修改后的完整代码如下：

cpty_fenergo = []
date = "2022-10-07"

cdw_legalEntity = "http://svc-alt-uat-cdw/identifier/legalEntityClients/?yyyy-mm-dd=2022-10-07&limit=10"

download_file(
    cdw_legalEntity, countsCDWdatasimple(cdw_legalEntity), "cdw_legalEntity.csv"
)

f_cpty = (
    open(f"{tempdir}\\cdw_legalEntity.csv", encoding="cp437").read().replace("\n", "")
)
json_cpty = json.loads(f_cpty, strict=False)

for doc in json_cpty:
    # 获取当前对象下所有符合条件的accountId值，无匹配时返回空列表
    account_ids = jmespath.search("identifier[?_name == 'accountId'].#value", doc) or []
    # 将所有值添加到结果列表
    cpty_fenergo.extend(account_ids)

# 去重处理
cpty_fnId = list(set(cpty_fenergo))

print(len(cpty_fnId))
print(cpty_fnId)

3. 代码说明 #