Question 109110 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Extracting multiple fields from a JSON in python

I have the following JSON object:

{
  'organisations': {
    'total-items': '41477',
    'organisation': [
      {
        'mappings': None,
        'active-request': 'false',
        'identifiers': {
          'identifier': {
            'code': 'ORG-100023310',
            'code-system': '100000167446',
            'code-system-name': ' OMS Organization Identifier'
          }
        },
        'name': 'advanceCOR GmbH',
        'operational-attributes': {
          'created-on': '2016-10-18T15:38:34.322+02:00',
          'modified-on': '2022-11-02T08:23:13.989+01:00'
        },
        'locations': {
          'location': [
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100052061'
                },
                'id': 'LOC-100052061'
              }
            },
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100032442'
                },
                'id ': 'LOC-100032442'
              }
            },
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100042003'
                },
                'id': 'LOC-100042003'
              }
            }
          ]
        },
        'organisation-id': {
          'link': {
            'rel': 'self',
            'href': 'https://v1 /organisations/ORG-100023310'
          },
          'id': 'ORG-100023310'
        },
        'status': 'ACTIVE'
      },
      {
        'mappings': None,
        'active-request': 'false',
        'identifiers': {
          'identifier': {
            'code': 'ORG-100004261',
            'code-system': '100000167446',
            'code-system-name': 'OMS organization Identifier'
          }
        },
        'name': 'Beacon Pharmaceuticals Limited',
        'operational-attributes': {
          'created-on': '2016-10-18T14:48:16.293+02:00',
          'modified-on': '2022-10-12T08:26:24.645+02:00'
        },
        'locations': {
          'location': [
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100005615'
                },
                'id': 'LOC-100005615'
              }
            },
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100000912'
                },
                'id': 'LOC-100000912'
              }
            },
            {
              'location-id': {
                'link': {
                  'href': 'https://v1/locations/LOC-100043831'
                },
                'id': 'LOC-100043831'
              }
            }
          ]
        },
        'organisation-id': {
          'link': {
            'rel': 'self',
            'href': 'https://v1/organisations/ORG-100004261'
          },
          'id': 'ORG-100004261'
        },
        'status': 'ACTIVE'
      },

I would like to fetch following fields ‘code, name, location, status’ for all the org_id (type of d: class ‘list’, type of d[0) is class ‘dict’. I am using following lines of code however could not fetch all the organisation ids and location_id (only 1 I can get) (shown in current output). How can I get information for all the org_id (shown in expected output)?

Code:

with open('organisations.json', encoding='utf-8') as f:
    d = json.load(f)
    print (d[0]['organisations']['organisation'][2]['identifiers']['identifier']['code'])   #code
    print (d[0]['organisations']['organisation'][2]['name'])                                #name
    print (d[0]['organisations']['organisation'][2]['locations'])                       #location
    print (d[0]['organisations']['organisation'][2]['status'])                          #status

current output:

ORG-100023310
advanceCOR GmbH
{'location': {'location-id': {'link': {'href': 'https://v1/locations/LOC-100052061'}, 'id': ' LOC-100052061'}}}
ACTIVE

Expected output:

org_id        org_name                         location_id                                  status
ORG-100023310 advanceCOR GmbH                  LOC-100052061, LOC-100032442, LOC-100042003  ACTIVE
ORG-100004261 Beacon Pharmaceuticals Limited   LOC-100005615, LOC-100000912, LOC-100043831         ACTIVE
Tags:

4

Answers


  1. 0

    You could use a for loop to loop through all your organization,

    with open('organisations.json', encoding='utf-8') as f:
    d = json.load(f)

    # assuming above code is your working code
    # assign organization list in a variable
    organization_list = d[0]['organisations']['organisation']

    # now loop through that list
    for organization in organization_list:
        name = organization['name']
        # fetch other values similary

        # print the values here
        print(name)
    Login or Signup to reply.
  2. 0

    You are getting only 1 row as output because you are printing only index 2. Hope the following will be helpful.

    with open('data.json') as file:
    data = json.load(file)
    
result = pd.DataFrame( columns=['org_id', 'org_name', "location_id", "status"])
organizations = data['organisations']['organisation']
for organization in organizations:
    record = {
        'org_id': organization['identifiers']['identifier']['code'],
        'org_name': organization['name'],
        'location_id': organization['locations'],
        'status': organization['status']
    }
    result_row = pd.DataFrame.from_records([record])
    result = pd.concat([result, result_row])
print(result)
    Login or Signup to reply.
  3. 0

    The following extracts the information for both organizations.

    import json

with open('organisations.json', encoding='utf-8') as f:
    d = json.load(f)
    
    for organisation in d['organisations']['organisation']:
        print(organisation['identifiers']['identifier']['code'])    #code
        print(organisation['name'])                                 #name
        print(organisation['locations']['location'])                #location
        print(organisation['status'])                               #status

    Output

    ORG-100023310
ORG-100023310
advanceCOR GmbH
[{'location-id': {'link': {'href': 'https://v1/locations/LOC-100052061'}, 'id': 'LOC-100052061'}}, {'location-id': {'link': {'href': 'https://v1/locations/LOC-100032442'}, 'id ': 'LOC-100032442'}}, 
{'location-id': {'link': {'href': 'https://v1/locations/LOC-100042003'}, 'id': 'LOC-100042003'}}]
ACTIVE
ORG-100004261
ORG-100004261
Beacon Pharmaceuticals Limited
[{'location-id': {'link': {'href': 'https://v1/locations/LOC-100005615'}, 'id': 'LOC-100005615'}}, {'location-id': {'link': {'href': 'https://v1/locations/LOC-100000912'}, 'id': 'LOC-100000912'}}, {'location-id': {'link': {'href': 'https://v1/locations/LOC-100043831'}, 'id': 'LOC-100043831'}}]
ACTIVE
    Login or Signup to reply.
  4. 0

    Your attempt has two flaws; as others have pointed out, you are only extracting the last item by hardcoding ['organisation'][2], and you are not doing anything to serialize the dictionary of locations into a string.

    Here is a modified version of your code which produces the desired output.

    import json

fmt = "{0:14}{1:33}{2:45}{3}"
print(fmt.format("org_id", "org_name", "location_id", "status"))

with open('organisations.json', encoding='utf-8') as f:
    d = json.load(f)
    for org in d['organisations']['organisation']:
        locations = ", ".join(loc['location-id']['id'] for loc in org['locations']['location'])
        print(fmt.format(
            org['identifiers']['identifier']['code'],
            org['name'], locations, org['status']))

    The "JSON" sample you posted was not really valid JSON, so I had to make some changes to it for the code to work.

    If you are new to Python, you might want to avoid the dictionary comprehension loc['location-id']['id'] for loc in org['locations']['location'] in favor of its longhand articulation:

    locations = []
for loc in org['locations']['location']:
    locations.append(loc['location-id']['id'])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search