Best Method For Query Content In Large JSON Files

04-15-2016

Registered User

227, 3

Join Date: Dec 2007

Last Activity: 3 February 2020, 9:46 AM EST

Location: Washington D.C

Posts: 227

Thanks Given: 31

Thanked 3 Times in 3 Posts

Best Method For Query Content In Large JSON Files

I wanted to know what is the best way to query json formatted files for content? Ex. Data
https://usn.ubuntu.com/usn-db/database-all.json.bz2
When looking at keys as in:

Code:

import json
json_data = json.load(open('database-all.json'))
for keys in json_data.iterkeys():
    print 'Keys--> {} \n'.format(keys)
Keys--> 688-1 

Keys--> 1579-1 

Keys--> 2870-2 

Keys--> 2870-1 

Keys--> 1107-1 
blah blah blah

I can see the data structure

Code:

json_data['1004-1']
{u'action': u'In general, a standard system update will make all the necessary changes.\n',
 u'cves': [u'CVE-2010-3082'],
 u'description': u'It was discovered that Django did not properly sanitize the cookie value\nwhen applying CSRF protections resulting in a cross-site scripting (XSS)\nvulnerability. With cross-site scripting vulnerabilities, if a user were\ntricked into viewing server output during a crafted server request, a\nremote attacker could exploit this to modify the contents, or steal\nconfidential data, within the same domain.\n',
 u'id': u'1004-1',
 u'isummary': u'Django could be made to insert arbitrary content into web forms.\n',
 u'releases': {u'maverick': {u'archs': {u'all': {u'urls': {u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django-doc_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'5f3ed62933c8f4970101ead2d57d7d4f',
       u'size': 1905856},
      u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'8c85dcb4ab4d9701cd546e2e119ae4e3',
       u'size': 4212250}}},
    u'source': {u'urls': {u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1.debian.tar.gz': {u'md5': u'2e8c4c95d6d40cce184131f1001a01a2',
       u'size': 18499},
      u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1.dsc': {u'md5': u'a5cb861587d952430ae73da49a9680cf',
       u'size': 2249},
      u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3.orig.tar.gz': {u'md5': u'10bfb5831bcb4d3b1e6298d0e41d6603',
       u'size': 6306760}}}},
   u'binaries': {u'python-django': {u'version': u'1.2.3-1ubuntu0.1'}},
   u'sources': {u'python-django': {u'description': u'High-level Python web development framework',
     u'version': u'1.2.3-1ubuntu0.1'}}}},
 u'summary': u'python-django vulnerability',
 u'timestamp': 1287004073.841373,
 u'title': u'Django vulnerability'}

and can access each level easily as in:

Code:

json_data['1004-1']['action']
u'In general, a standard system update will make all the necessary changes.\n
json_data['1004-1']['releases']['maverick']
{u'archs': {u'all': {u'urls': {u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django-doc_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'5f3ed62933c8f4970101ead2d57d7d4f',
     u'size': 1905856},
    u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'8c85dcb4ab4d9701cd546e2e119ae4e3',
     u'size': 4212250}}},
  u'source': {u'urls': {u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1.debian.tar.gz': {u'md5': u'2e8c4c95d6d40cce184131f1001a01a2',
     u'size': 18499},
    u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1.dsc': {u'md5': u'a5cb861587d952430ae73da49a9680cf',
     u'size': 2249},
    u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3.orig.tar.gz': {u'md5': u'10bfb5831bcb4d3b1e6298d0e41d6603',
     u'size': 6306760}}}},
 u'binaries': {u'python-django': {u'version': u'1.2.3-1ubuntu0.1'}},
 u'sources': {u'python-django': {u'description': u'High-level Python web development framework',
   u'version': u'1.2.3-1ubuntu0.1'}}}

but when I attempt to iterate through the keys and values, I cant do so easy. My goal is to simply loop through this entire json structured file and print out values that I would like to see as in:

Code:

json_data['1004-1']['cves']
[u'CVE-2010-3082']
json_data['1004-1']['releases']['maverick']['archs']['all']['urls']
{u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django-doc_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'5f3ed62933c8f4970101ead2d57d7d4f',
  u'size': 1905856},
 u'http://security.ubuntu.com/ubuntu/pool/main/p/python-django/python-django_1.2.3-1ubuntu0.1_all.deb': {u'md5': u'8c85dcb4ab4d9701cd546e2e119ae4e3',
  u'size': 4212250}}

I have made many attempts to use the functions in the "json" module:

Code:

for keys, values in json_data.iteritems():
    print 'Keys--> {} \n'.format(keys)
    for val in values.iteritems():
        print u'Values--> {} \n'.format(val)
Keys--> 2251-1 

Values--> (u'description', u'A bounds check error was discovered in the socket filter subsystem of the\nLinux kernel. A local user could exploit this flaw to cause a denial of\nservice (system crash) via crafted BPF instructions. (CVE-2014-3144)\n\nA remainder calculation error was discovered in the socket filter subsystem\nof the Linux kernel. A local user could exploit this flaw to cause a denial\nof service (system crash) via crafted BPF instructions. (CVE-2014-3145)\n')
blah blah blah

but doesnt work when I attempt to do any type of searching as in:

Code:

In [65]: for keys, values in json_data.iteritems():
    print 'Keys--> {} \n'.format(keys)
    for val in values.iteritems():
        print u'Values--> {} \n'.format(val['releases']['maverick']['archs']['all']['urls'])
   ....:         
Keys--> 756-1 

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-65-ea4c264328b0> in <module>()
      2     print 'Keys--> {} \n'.format(keys)
      3     for val in values.iteritems():
----> 4         print u'Values--> {} \n'.format(val['releases']['maverick']['archs']['all']['urls'])
      5 

TypeError: tuple indices must be integers, not str

God help me !!!!!!!!!!!!

Last edited by metallica1973; 04-15-2016 at 06:47 PM..

metallica1973

View Public Profile for metallica1973

Find all posts by metallica1973

Programming

Best Method For Query Content In Large JSON Files

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Large Variable content size caveats?

Discussion started by: stomp

2. Shell Programming and Scripting

Split JSON to different data files

Discussion started by: manas_ranjan

3. Shell Programming and Scripting

Most efficient method to extract values from text files

Discussion started by: h0x0r21

4. UNIX for Advanced & Expert Users

best method to compress files in aix 5.3

Discussion started by: systemoper

5. UNIX for Advanced & Expert Users

best method to compare 2 big files in unix

Discussion started by: rashmisb

6. Shell Programming and Scripting

Query for replacing a string and keeping the non-replaced content

Discussion started by: rahulfhp

7. Web Development

Content Management System for uploading large files

Discussion started by: z1dane

8. Red Hat

Method to Unpack cpio files

Discussion started by: William1482

9. Shell Programming and Scripting

Removing lines from large files.. quickest method?

Discussion started by: frustrated1

10. Solaris

Secure method - get files - no ssh/scp

Discussion started by: frustrated1