could not Extract Value from Json with python

Question

This is not the duplicate ticket.

I have checked the similar threads, like:

Parsing JSON with Python: TypeError: list indices must be integers, not str

Python Extract Value from Json

But none of them work for me.

The background of my question:

I use the REST API Groups - Get Groups with filter:

GET https://api.powerbi.com/v1.0/myorg/groups?$filter={$filter}&$top={$top}&$skip={$skip}

My code:

import json, requests, pandas as pd
try:
    from azure.identity import ClientSecretCredential
except Exception:
    #  !pip install azure.identity
     from azure.identity import ClientSecretCredential


tenant = 'xxxxxxxxxx'
client = 'yyyyyyyyyyy'
client_secret = 'zzzzzzzzzzzzzz'
api = 'https://analysis.windows.net/powerbi/api/.default'


# Generates the access token for the Service Principal
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
                                                        tenant_id = tenant,
                                                        client_id = client,
                                                        client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token

print('\nSuccessfully authenticated.')   

base_url = 'https://api.powerbi.com/v1.0/myorg/'
header = {'Authorization': f'Bearer {access_token}'}

base_url_expand = f'{base_url}groups?$filter=name%20eq%20%27TestName%27'

# HTTP GET Request
groups = requests.get(base_url_expand, headers=header)
# Response code (200 = Success; 401 = Unauthorized; 404 = Bad Request)
print(groups)

And the result of the groups is <Response [200]>.

Then I want to get the id based on the name in the Response body:

So, I use the following the code to get the content of the groups:

try:
    groups = json.loads(groups.content)

    # Pretty-prints the JSON
    print(json.dumps(groups, indent=4, sort_keys=True))
        
except Exception as e:
    print('\nRequest failed:', e)

The print result is：

{
    "@odata.context": "http://wabi-south-east-asia-redirect.analysis.windows.net/v1.0/myorg/$metadata#groups",
    "@odata.count": 1,
    "value": [
        {
            "id": "bf8f466d-35b0-4620-a11e-xxxxxxx",
            "isOnDedicatedCapacity": false,
            "isReadOnly": false,
            "name": "TestName",
            "type": "Workspace"
        }
    ]
}

However, I could not extract value id from the Json file.

Answer 1

Let's start by taking a look at what exactly you get when you do

groups = requests.get(base_url_expand, headers=header)
[...]
try:
    groups = json.loads(groups.content)

response.content returns the content of the response, in bytes. It refers to Binary Response content. You can check it by

print("type groups:",type(groups))

Output:

<class 'bytes'>

If you also do print(groups) you will see a b’ at the start of the output. That means the reference is a bytes object. Now , that is not a json object, so you can't access it as a dict To access it as a dict, you have to convert it to a dict first. One way is to do it directly

groups = response.json())

response.json() returns a JSON object of the results, or it raises an error if the result was not written in JSON.

This time you have a dict

print(type(response.json()))

Output:

<class 'dict'>

Then you can find your way to loop and access the keys, for example

groups = response.json()
for i in groups['value']:
    print(i['id'])

Answer 2

I hope this should work:

try:
    groups = groups.json()

    # Pretty-prints the JSON
    print(json.dumps(groups, indent=4, sort_keys=True))
    
except Exception as e:
    print('\nRequest failed:', e)

Answer 3

After this line groups = json.loads(groups.content), try the below statement

print(groups['value'][0]['id'])

If the above works, you can loop through the groups.value and print all ids as below.

for group in groups['value']:
    print(group['id'])

Answer 4

It's just straightforward to read the id from the response

Modified in your code

try:
    groups_data = json.loads(groups.content)
    id_value = groups_data.get('value', [])[0].get('id')
    print(id_value)
except Exception as e:
    print('\nRequest failed:', e)

It's a good approach to use the get method in the dictionary.

My approach

...... Rest of your code ......
base_url_expand = f'{base_url}groups?$filter=name%20eq%20%27TestName%27'

# HTTP GET Request
response = requests.get(base_url_expand, headers=header)

try:
    data = response.json()
    id_value = data.get('value', [])[0].get('id')
    print(id_value)
except Exception as e:
    print('\nRequest failed:', e)

Since the response object has the inbuild json method to read the json you can use that.

Answer 5

You are dealing with a Response object, which, along the actual response content, contains more information such as status code, response headers, URL... If you are sure the returned data is, and always will be a JSON, you can access it directly:

data = response.json()

In your case, getting the id would be as easy as

for item in data['value']:
    id_ = item['id']

I have used a loop because 'value' is a list, and id_ variable name because id is already a built-in name.

Answer 6

field_list is equal to data['value'] and data['value'] is equal to [{'id': 'bf8f466d-35b0-4620-a11e-3xxxxxxx', 'isReadOnly': False, 'isOnDedicatedCapacity': False, 'type': 'Workspace', 'name': 'testname'}]

so when you loop field_list first element is {'id': 'bf8f466d-35b0-4620-a11e-3xxxxxxx', 'isReadOnly': False, 'isOnDedicatedCapacity': False, 'type': 'Workspace', 'name': 'testname'}

so print as print(fields['id'])

Solutions :

print(data ['value'][0]['id'])

or

data = groups.json()
field_list = data['value']
for fields in field_list:
    print(fields['id'])

or

data = groups.json()
field_list = data['value']
for i in range(len(field_list)):
    print(field_list[i]['id'])

Answer 7

the following should work to get the id:

import json

groups = {
    "@odata.context": "http://wabi-south-east-asia-redirect.analysis.windows.net/v1.0/myorg/$metadata#groups",
    "@odata.count": 1,
    "value": [
        {
            "id": "bf8f466d-35b0-4620-a11e-3xxxxxxx",
            "isReadOnly": False,
            "isOnDedicatedCapacity": False,
            "type": "Workspace",
            "name": "testname",
        }
    ],
}


data = json.loads(json.dumps(groups, indent=4))

for fields in data["value"]:
    print(fields["id"])