35

I am using the python package pymongo to retrieve data from a mongodb database.

>>> r = collection.find()   # returns an object of class 'Cursor'

Then I convert to a list

>>> l = list(r)             # returns a 'list' of 'dict'

here is what print(l) returns:

>>> [{u'date': datetime.datetime(2009, 11, 10, 10, 45), u'_id': 1, u'name': u'name1', u'value': 11},{u'date': datetime.datetime(2013, 11, 10, 10, 45), u'_id': 2, u'name': u'name2', u'value': 22}]

Now I need to convert to JSON so that I can manipulate it. 

>>> json.dumps(l)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/json/__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "/usr/lib/python2.7/json/encoder.py", line 201, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/lib/python2.7/json/encoder.py", line 264, in iterencode
    return _iterencode(o, 0)
  File "/usr/lib/python2.7/json/encoder.py", line 178, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: datetime.datetime(2009, 11, 12, 11, 14) is not JSON serializable

I have also tried to follow http://api.mongodb.org/python/1.7/api/pymongo/json_util.html without success: Edit: the recent version of the link is http://api.mongodb.org/python/current/api/bson/json_util.html

>>> json.dumps(l, default=json_util.default)  
Traceback (most recent call last):  
  File "<stdin>", line 1, in <module>  
NameError: name 'json_util' is not defined

Note: precisely I need to push this result to R using the R package rPython and its function rPython::python.get("l")

Side Question: What is the u (u'Date', u'name', etc..) before each field in the list of dict?

William Bettridge-Radford
  • 2,485
  • 3
  • 20
  • 33
RockScience
  • 17,932
  • 26
  • 89
  • 125

5 Answers5

66

The pymongo documentation you pointed is obsolete. If you're using version 1.7 I recommend updating. With a more recent version you can do this:

from bson.json_util import dumps

dumps(l)

https://pymongo.readthedocs.io/en/stable/api/bson/json_util.html

Side answer: u'name', u'date', u'_id' etc are the names of the fields of the document on the database.

Rafa Viotti
  • 9,998
  • 4
  • 42
  • 62
  • Thanks! regarding the Side question, what does the u' mean? I thought 'name','date','_id' were the field names – RockScience Oct 30 '13 at 08:16
  • 1
    @rock `u'literal'` is an Unicode string in Python 2. They are much like strings, but are specified in the syntax using a preceding `u`. Pymongo is decoding the field names to Unicode, that is why you are seeing them in that form. If you field names contain only ASCII characters, you don't need to worry about that. – Rafa Viotti Oct 30 '13 at 14:07
  • ok thanks, will read about that. I might have to play with this format as my fields also have dates – RockScience Oct 31 '13 at 03:31
  • Thank you I use this code on bottle framework: @route('/admin/users', method=['OPTIONS', 'GET']) def get_users(): entity = db['users'].find() l = list(entity) return dumps(l) – Gabriel Mar 08 '14 at 02:53
  • dumps method taking lot of time for 100 documents – Geetesh Jun 04 '19 at 15:14
13
from bson import json_util



json.dumps(result,default=json_util.default)
DhruvPathak
  • 42,059
  • 16
  • 116
  • 175
2

In my situation, this error is due to mongo DB id object in flask all you have to do is convert id (NOTE: if you need id convert it else you can pop it too) I'm sharing my solution which I figured out hope this helps someone

from flask import jsonify

def get_data(self,data):
     data['_id'] = str(data['_id'])
     return data

app =  Flask(__name__)

@app.route('/')
def apimethod():
     temp = [self.get_data(i) for i in self.collection.find()]
     return jsonify(temp)

also dumps from pymongo don't help alot

from bson.json_util import dumps,loads

because it is returning a string instead of dict which was expected in my situation to create API and I have to load again if I did dumps.

Jackson Jegatheesan
  • 630
  • 7
  • 15
  • 1
    This is the correct answer if someone wants to have a JSON returned from the API instead of a string. – Saif Asif Dec 23 '20 at 22:55
0

I was facing the same issue, I wrote a code that converts document to dictionary. You can use that for reference. Pass the object obtained by find_one() into documentToJson() method and the results of find() into convertDocumentsToJson. There is type in the name Json, instead the code converts to Dict rather than json.

from bson.json_util import dumps

class UtilService:

def __init__(self):
    pass

@staticmethod
def pinCodeParser(path):
    location = {}
    f = open(path)
    for line in f:
        words = line.split()
        location[words[1]] = (words[-3],words[-2])
    return location

@staticmethod
def listHelper(str):
    s = []
    str = str.split(',')
    for e in str:
        s.append(e.replace("[","").replace("]",""))
    return s

@staticmethod
def parseList(str):
    if ',' in str:
        return UtilService.listHelper(str)
    return str

@staticmethod
def trimStr(str):
    return str.replace('"','')

@staticmethod
def documentToJson(document):
    document = eval(dumps(document))
    mp = {}
    for key, value in document.iteritems():
        if "_id" in key:
            mp["id"] = str(value["$oid"])
        else:
            mp[ UtilService.trimStr(key) ] = UtilService.parseList( value )
    return mp

@staticmethod
def convertDocumentsToJson(documents):
    result = []
    for document in documents:
        result.append(UtilService.documentToJson(document))
    return result
rbansal
  • 1,184
  • 14
  • 21
0

This thread helped me - thank you.

Wanted to share my final solution to get the JSON back into a JSON/Dictionary Object: (Based on your example) 

from bson.json_util import dumps, loads
r = collection.find()
l = list(r) # Converts object to list
d = dumps(l) # Converts to String
dict_needed = loads(d[0]) # Serializes String and creates dictionary

Now you have the JSON in a dictionary object and can edit as needed.

Jonathan Aspeling
  • 9
  • 3