I am having problems getting some values using jmespath.search().
Just to put it in context, I am downloading all the information from my request in a CSV file. I then upload this as a JSON and using JMESPath, I wish to get the values.
I want to get the #value where '_instrumentIdScheme': 'mhi:MHILIST'
json fixed:
[
{
"_fpmlVersion": "5-6",
"header": {
"messageType": "PrevDayCloseBond",
"sendTo": [
{
"#value": "Anvil"
}
],
"creationTimestamp": "2021-09-28T06:00:00.000Z"
},
"m:asOfDate": {
"#value": "2021-09-28T00:00:00.000Z"
},
"_xmlns": "http://www.fpml.org/FpML-5/reporting",
"_xmlns:m": "urn:com.mizuho.bdm",
"_xmlns:mhi": "urn:com.mizuho.bdm.mhi",
"_xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"_xsi:schemaLocation": "http://www.fpml.org/FpML-5/reporting http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/fpml/fpml-5-6-reporting.xsd urn:com.mizuho.bdm http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/fpml/mizuho-fpml.xsd urn:com.mizuho.bdm.mhi http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/mhi/fpml/mhi-fpml.xsd",
"m:assetPricing": [
{
"m:pricingSource": [
{
"#value": "LON-XEN-BBG"
},
{
"#value": "BGN",
"_pricingSourceScheme": "mizuho:bloomberg-source"
}
],
"m:instrumentId": [
{
"#value": "100001380992",
"_instrumentIdScheme": "mhi:MHILIST"
},
{
"#value": "100001380992",
"_instrumentIdScheme": "mhsa:instrument-id"
}
],
"m:currency": {
"#value": "USD"
},
"m:price": [
{
"value": 140.78125,
"measureType": {
"#value": "Bid Price",
"_assetMeasureScheme": "mizuho:price-type"
}
},
{
"value": 140.875,
"measureType": {
"#value": "Mid Price",
"_assetMeasureScheme": "mizuho:price-type"
}
},
{
"value": 140.96875,
"measureType": {
"#value": "Offer Price",
"_assetMeasureScheme": "mizuho:price-type"
}
}
]
}
],
"m:pricingDate": "2021-09-28T00:00:00.000Z"
}
]
replace all simple quotes by double quotes
to select all #value with the condition:
def flatten(container):
for i in container:
if isinstance(i, (list,tuple)):
for j in flatten(i):
yield j
else:
yield i
str = """
[
{
"_fpmlVersion": "5-6",
"header": {
"messageType": "PrevDayCloseBond",
"sendTo": [
{
"#value": "Anvil"
}
],
"creationTimestamp": "2021-09-28T06:00:00.000Z"
},
"m:asOfDate": {
"#value": "2021-09-28T00:00:00.000Z"
},
"_xmlns": "http://www.fpml.org/FpML-5/reporting",
"_xmlns:m": "urn:com.mizuho.bdm",
"_xmlns:mhi": "urn:com.mizuho.bdm.mhi",
"_xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"_xsi:schemaLocation": "http://www.fpml.org/FpML-5/reporting http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/fpml/fpml-5-6-reporting.xsd urn:com.mizuho.bdm http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/fpml/mizuho-fpml.xsd urn:com.mizuho.bdm.mhi http://svc-bdmentity01p:8080/schema/7.2.0/com/mizuho/bdm/mhi/fpml/mhi-fpml.xsd",
"m:assetPricing": [
{
"m:pricingSource": [
{
"#value": "LON-XEN-BBG"
},
{
"#value": "BGN",
"_pricingSourceScheme": "mizuho:bloomberg-source"
}
],
"m:instrumentId": [
{
"#value": "100001380992",
"_instrumentIdScheme": "mhi:MHILIST"
},
{
"#value": "100001380992",
"_instrumentIdScheme": "mhsa:instrument-id"
}
],
"m:currency": {
"#value": "USD"
},
"m:price": [
{
"value": 140.78125,
"measureType": {
"#value": "Bid Price",
"_assetMeasureScheme": "mizuho:price-type"
}
},
{
"value": 140.875,
"measureType": {
"#value": "Mid Price",
"_assetMeasureScheme": "mizuho:price-type"
}
},
{
"value": 140.96875,
"measureType": {
"#value": "Offer Price",
"_assetMeasureScheme": "mizuho:price-type"
}
}
]
}
],
"m:pricingDate": "2021-09-28T00:00:00.000Z"
}
]
"""
str = str.replace("\n", "").replace("\t", "")
str = json.loads(str)
#print(str)
valueslist = jmespath.search('[]["m:assetPricing"][][]."m:instrumentId"[?"_instrumentIdScheme" == `mhi:MHILIST`].["#value"]', str)
#print(valueslist)
values = list(flatten(valueslist))
print(values)
result:
['100001380992']