CODEWITHSUNDEEP
pythonmongodbpython-3.xpymongo
aquatic7
aquatic7

Reputation: 635

How do I reformat this json for database import?

I have these "json" files that I like to insert into my mongodb database.

An example of one is: http://s.live.ksmobile.net/cheetahlive/de/ff/15201023827214369775/15201023827214369775.json

The problem is, that it is formated like this:

   { "channelType":"TEMPGROUP", ... } # line 1
   { "channelType":"TEMPGROUP", ... } # line 2

So instead of inserting it as 1 document in the DB, it insert every single line as 1 entry. That ends up with what should be 3 documents from 3 "json" files in the database become 1189 documents in the database instead.

How can I insert the whole content of the ".json" into one document?

My code is:

replay_url = "http://live.ksmobile.net/live/getreplayvideos?"

userid = 969730808384462848

url2 = replay_url + urllib.parse.urlencode({'userid': userid}) + '&page_size=1000'

raw_replay_data = requests.get(url2).json()

for i in raw_replay_data['data']['video_info']:
    url3 = i['msgfile']
    raw_message_data = urllib.request.urlopen(url3)
    for line in raw_message_data:
        json_data = json.loads(line)
        messages.insert_one(json_data)
        print(json_data)

Update to give more information to answer

messages.insert(json_data) gives this error:

Traceback (most recent call last):
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/collection.py", line 633, in _insert
    blk.execute(concern, session=session)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/bulk.py", line 432, in execute
    return self.execute_command(generator, write_concern, session)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/bulk.py", line 329, in execute_command
    raise BulkWriteError(full_result)
pymongo.errors.BulkWriteError: batch op errors occurred

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/import_messages_dev.py", line 43, in <module>
    messages.insert(json_data)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/collection.py", line 2941, in insert
    check_keys, manipulate, write_concern)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/collection.py", line 635, in _insert
    _raise_last_error(bwe.details)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/helpers.py", line 220, in _raise_last_error
    _raise_last_write_error(write_errors)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/helpers.py", line 188, in _raise_last_write_error
    raise DuplicateKeyError(error.get("errmsg"), 11000, error)
pymongo.errors.DuplicateKeyError: E11000 duplicate key error index: liveme.messages.$_id_ dup key: { : ObjectId('5aa2fc6f5d60126499060949') }

messages.insert_one(json_data) gives me this error:

Traceback (most recent call last):
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/import_messages_dev.py", line 43, in <module>
    messages.insert_one(json_data)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/collection.py", line 676, in insert_one
    common.validate_is_document_type("document", document)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/common.py", line 434, in validate_is_document_type
    "collections.MutableMapping" % (option,))
TypeError: document must be an instance of dict, bson.son.SON, bson.raw_bson.RawBSONDocument, or a type that inherits from collections.MutableMapping

messages.insert_many(json_data) gives me this error:

Traceback (most recent call last):
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/import_messages_dev.py", line 43, in <module>
    messages.insert_many(json_data)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/collection.py", line 742, in insert_many
    blk.execute(self.write_concern.document, session=session)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/bulk.py", line 432, in execute
    return self.execute_command(generator, write_concern, session)
  File "/media/anon/06bcf743-8b4d-409f-addc-520fc4e19299/PycharmProjects/LiveMe/venv1/lib/python3.6/site-packages/pymongo/bulk.py", line 329, in execute_command
    raise BulkWriteError(full_result)
pymongo.errors.BulkWriteError: batch op errors occurred

messages.insert and messages.insert_many both insert 1 line and throw the error.

Upvotes: 1

Views: 189

Answers (1)

match
match

Reputation: 11060

These files obviously do not contain properly formatted json - rather they contain a separate object on each line.

To turn them into valid json, you probably want a list of objects, i.e.:

[{ "channelType":"TEMPGROUP", ... },
 { "channelType":"TEMPGROUP", ... }]

You can achieve this by doing:

for i in raw_replay_data['data']['video_info']:
    url3 = i['msgfile']
    raw_message_data = urllib.request.urlopen(url3)
    json_data = []
    for line in raw_message_data:
        json_data.append(json.loads(line))
        messages.insert_one(json_data)
        print(json_data)

Upvotes: 1

Related Questions