NOTE: Your JSON response from MongoDB is not actually valid. JSON requires double-quotes ("), not single-quotes (').
I'm not sure why your response has single-quotes instead of double-quotes but from the looks of it you can replace them and then just use the built-in json module:
from __future__ import print_function
import json
response = """{
'ok': 1.0,
'result': [
{
'total': 142250.0,
'_id': 'BC'
},
{
'total': 210.88999999999996,
'_id': 'USD'
},
{
'total': 1065600.0,
'_id': 'TK'
}
]
}"""
# JSON requires double-quotes, not single-quotes.
response = response.replace("'", '"')
response = json.loads(response)
for doc in response['result']:
print(doc['_id'], doc['total'])
NOTE: Your JSON response from MongoDB is not actually valid. JSON requires double-quotes ("), not single-quotes (').
I'm not sure why your response has single-quotes instead of double-quotes but from the looks of it you can replace them and then just use the built-in json module:
from __future__ import print_function
import json
response = """{
'ok': 1.0,
'result': [
{
'total': 142250.0,
'_id': 'BC'
},
{
'total': 210.88999999999996,
'_id': 'USD'
},
{
'total': 1065600.0,
'_id': 'TK'
}
]
}"""
# JSON requires double-quotes, not single-quotes.
response = response.replace("'", '"')
response = json.loads(response)
for doc in response['result']:
print(doc['_id'], doc['total'])
The response you are getting from the mongodb seems to be the compatible to put for the dictionary type object. as
{
'ok': 1.0, 'result': [
{
'total': 142250.0,
'_id': 'BC'
},
{
'total': 210.88999999999996,
'_id': 'USD'
},
{
'total': 1065600.0,
'_id': 'TK'
}
]
}
Instead of putting it into multiline string and replacing single quotes in double quotes, can't we directly assign it to the dict type object. and perform further operation on it like:
json_data = {
'ok': 1.0,
'result':
[
{
'total': 142250.0,
'_id': 'BC'
},
{
'total': 210.88999999999996,
'_id': 'USD'
},
{
'total': 1065600.0,
'_id': 'TK'
}
]
}
And:
for data in json_data['result']:
print(data['total'], data['_id'])
Hi all,
Long time programmer here, but new to Python. This will be a long and, I think, complicated issue, so appreciate anyone who reads through it all and has any suggestions. I've looked up different ways to pull this data and don't seem to be making any progress. I'm sure there's a much better way.
I'm writing a program that will connect to our library to pull a list of everything we have checked out and I want to output a sorted list by due date and whether it has holds or not. I've got the code working to log in and pull a json data structure, but I cannot get it to export the data in the correct order. The json data is(to me) hideously complex with some data(due date) in one section and other data in another section. I'm able to pull the fields I want, but keeping them together is proving challenging.
For example, the title and subtitle are in the 'bibs/briefinfo' section with a key value of 'title' or 'subtitle'. Due Date is also in the 'checkouts' section with a key value of 'dueDate'. When I loop through them, though, the Titles are in one order, the due dates are in another order and the subtitles another.
I used BeautifulSoup because it's a webpage with json in it, so used BS to read the webpage.
I'm wanting to pull the following fields for each book so I can display the info for each book:
title, subtitle, contentType from briefinfo section
duedate from checkouts section heldcopies and availablecopies from the availability section
Here's the pertinent section of my code:
soup = BeautifulSoup(index_page.text, 'html.parser')
all_scripts = soup.find_all('script', {"type":"application/json"})
for script in all_scripts:
jsondata = json.loads(script.text)
print(jsondata)
output = []
for i in item_generator(jsondata, "bibTitle"):
ans = {i}
print(i)
output.append(ans)
for i in item_generator(jsondata, "dueDate"):
ans = {i}
output.append(ans)
print("Subtitle----------------------")
for i in item_generator(jsondata, "subtitle"):
ans = {i}
print(i)
output.append(ans)
print(output)Here's the json output from my print statement so I can see what I'm working with. I tried to format it so it's easier to read. I removed a lot of other elements to keep the size down. Hopefully I didn't break any of the brackets.
{
'app':
{
'coreCssFingerprint': '123123123',
'coreAssets':
{
'cdnHost': 'https://xyz.com',
'cssPath': '/dynamic_stylesheet',
'defaultStylesheet': 'xyz.css'
},
},
'entities':
{
'listItems': {},
'cards': {},
'accounts':
{
'88888888':
{'barcode': '999999999',
'expiryDate': None,
'id': 88888888,
}
},
'shelves':
{'88888888':
{'1111222222':
{
'id': 1111222222,
'metadataId': 'S00A1122334',
'shelf': 'for_later',
'privateItem': True,
'dateAdded': '2023-12-30',
},
}
},
'users':
{'88888888':
{ 'accounts': \[88888888\],
'status': 'A',
'showGroupingDebug': False,
'avatarUrl': '',
'id': 88888888,
}
},
'eventPrograms': {}, 'checkouts':
{'112233445566778899':
{
'checkoutId': '112233445566778899',
'materialType': 'PHYSICAL',
'dueDate': '2024-08-26',
'metadataId': 'S99Z000000',
'bibTitle': "The Lord of the Rings"
},
'998877665544332211':
{
'checkoutId': 998877665544332211',
'materialType': 'PHYSICAL',
'dueDate': '2024-08-26',
'metadataId': 'S88Y00000',
'bibTitle': 'The Lord of the Rings'
},
},
'eventSeries': {}, 'catalogBibs': {},'bibs':
{'S88Y00000':
{
'id': 'S88Y00000',
'briefInfo':
{
'superFormats': ['BOOKS', 'MODERN_FORMATS'],
'genreForm': [],
'callNumber': '123.456',
'authors': ['Tolkien, J.R.R.'],
'metadataId': 'S88Y00000',
'jacket':
{
'type': 'hardcover',
'local_url': None
},
'contentType': 'FICTION',
'format': 'BK',
'subtitle': 'The Two Towers',
'title': 'The Lord of the Rings',
'id': 'S88Y00000',
},
'availability':
{
'heldCopies': 0,
'singleBranch': False,
'metadataId': 'S88Y00000',
'statusType': 'AVAILABLE',
'totalCopies': 3,
'availableCopies': 2
}
},
'S77X12345':
{
'id': 'S77X12345',
'briefInfo':
{
'superFormats': ['BOOKS', 'MODERN_FORMATS'],
'genreForm': [],
'callNumber': '123.457',
'authors': ['Tolkien, J.R.R.'],
'metadataId': 'S77X12345',
'jacket':
{
'type': 'hardcover',
'local_url': None
},
'contentType': 'FICTION',
'format': 'BK',
'subtitle': 'The Fellowship of the Ring',
'title': 'The Lord of the Rings',
'id': 'S77X12345',
},
'availability':
{
'heldCopies': 0,
'singleBranch': False,
'metadataId': 'S77X12345',
'statusType': 'AVAILABLE',
'totalCopies': 2,
'availableCopies': 1
}
}
Anyone know of a better way to parse this data? Thanks!
Places is a list and not a dictionary. This line below should therefore not work:
print(data['places']['latitude'])
You need to select one of the items in places and then you can list the place's properties. So to get the first post code you'd do:
print(data['places'][0]['post code'])
I did not realize that the first nested element is actually an array. The correct way access to the post code key is as follows:
r = requests.get('http://api.zippopotam.us/us/ma/belmont')
j = r.json()
print j['state']
print j['places'][1]['post code']
Hello,
Looking for help/advice on parsing nested JSON.
Background:
I receive a TXT file everyday containing JSON structured data. I need to parse through all of the JSON structure it into an Excel format with nice columns and rows where every column is the key and every row is its value.
Current data structure:
{
"key": "value",
"key": "value",
"key": "{\"key\":value,\"key\":value,\"key\":[{\"key\":\"value\" etc etc
}Current process:
file_location = r'<location of txt file containing json>'
with open(file_location) as json_file:
data = json,load(json_file)This allows me access to the high level key-value pairs, but does not allow me access to the nested data.
I've tried a dozen different ways to access nested data and thus far have not been successful. The nested JSON is returning as a string like:
nested_json = data['<key>']
nested_data = json.loads(nested_json)
I tried the code above thinking I must have to manually flatten each level of nested data. But with multiple levels of nested data in various places throughout the entire file, this would take me forever.
An issue I keep bumping into is that the nested portion of the data returns as STRING type rather than a dictionary, preventing me from referencing an index like this:
print(data['<key>'][1])
If I try that, it simply returns the first character in the key itself.
Additional attempts:
df = pd.DataFrame.from_dict(pd.json_normalize(data), orient='columns') df = pd.json_normalize('<key'>)I have around 40 different scripts I've tried, read various blogs, websites, stackoverflow, etc. And I still don't understand what I'm doing wrong.
Can anyone help please?
*I would post the data itself but it's restricted data I can't post online. If you need to see more code attempts let me know and I can copy more here.
tl;dr - need to parse all JSON data included all nested key-value pairs and get them in an Excel like format (Pandas/csv/txt/etc. is perfectly fine).
Thank you!
So maybe I am burnt out on this but I looked around on stack overflow and couldn't really find an answer that made sense to me.
Im trying to work with this weather API that gives the forecast and it return this insane JSON block that I have no idea how to parse. I don't even know where dictionaries begin and end in it. So if I wanted to spit out the current temp and indicate that it is Fahrenheit from the below link how would I do that?
https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%20in%20(select%20woeid%20from%20geo.places(1)%20where%20text%3D%22milwaukee%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys
Hopefully someone can help, thanks
EDIT:
Thanks for the responses. I'm a bit further along than what I posted and should have indicated that, sorry.
Im at:
-
import requests
-
import json
-
url = 'crazy-string'
-
r= requests.get(url)
-
r_json = r.json()
I'm working on how to parse through r_json and get values for specific keys
EDIT 2:
corvus_cornix had the answer I was looking for :)
Whenever i'm trying to wrap my head around JSON I put it through a formatter like this one.
Then you can use json.loads() to parse it. You access it just like a dictionary. A good example can be found here
have you tried using the json module or are you trying to learn/practice doing the parse code yourself?