How can I get all data from JSON using JMESPath-CodePudding

I am trying to get all the data with the attribute _name equal to accountId.

I have managed to do it almost fine, the only issue is that from times to times times I have an object. with two _name attributes with the value accountId and for this reason the script only captures the first one.

Is there any way for me to get the whole 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"
      }
    ]
  }
]

This is my code so far:

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']

CodePudding user response：

If you want to experiment with jmespath queries, you can paste your data into the data field at https://jmespath.org and then enter queries into the query field. The results will update automatically. Using that tool, the following query seems to produce what you want:

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

Given your example input data, that produces:

[
  "IIALFSG400",
  "IIALILFEUL",
  "IIALFSG372",
  "DBALP29M",
  "DBLP29MTRI",
  "DBLP034TRI",
  "DBALALP034",
  "DBLP29RTRI",
  "DBALP29R",
  "DBLP29PTRI",
  "DBALP29P",
  "DBALALP032",
  "DBLP032TRI",
  "DBLP29XTRI",
  "DBALP29X",
  "DBLP031TRI",
  "DBALALP031"
]

Or in Python (where the file data.json contains the sample data from your question, fixed so that it's valid JSON):

import json
import jmespath


with open('data.json') as fd:
    doc = json.load(fd)

res = jmespath.search('''[].identifier[?_name == 'accountId'][]."#value"''', doc)
print(res)

This will print:

['IIALFSG400', 'IIALILFEUL', 'IIALFSG372', 'DBALP29M', 'DBLP29MTRI', 'DBLP034TRI', 'DBALALP034', 'DBLP29RTRI', 'DBALP29R', 'DBLP29PTRI', 'DBALP29P', 'DBALALP032', 'DBLP032TRI', 'DBLP29XTRI', 'DBALP29X', 'DBLP031TRI', 'DBALALP031']