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

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.

@HarshaBiyani, It is the output the value of the rest api Groups - Get Groups: learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups, I want to get the group`s Id by the name of the group. — Joy
– Joy, Commented Sep 19, 2022 at 10:00

Deep Bhatt · Accepted Answer · 2022-09-19 10:13:07Z

2

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"])

edited Sep 19, 2022 at 10:13

answered Sep 19, 2022 at 10:01

Deep Bhatt

3111 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joy Over a year ago

Thanks for your input, but I test it and it give the error "Request failed: Object of type Response is not JSON serializable"

Deep Bhatt Over a year ago

you must be using it somewhere else then! because if you just want to fetch id out of JSON, that works! and that's the title of your query here

Joy Over a year ago

The value of groups is not the directly JSON file, I use groups = json.loads(groups.content) to get it. Now, I do not know how to get the value of the ID in the Response body.

Deep Bhatt Over a year ago

@joy: I got your query now but I can suggest you the following: save your "groups" into a JSON file & then read that file. or another way is to convert JSON objects into a dataframe. try to do that.

Thavarasa Prasanth · Accepted Answer · 2022-09-19 10:13:18Z

2

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'])

answered Sep 19, 2022 at 10:13

Thavarasa Prasanth

663 bronze badges

1 Comment

Joy Over a year ago

Thanks for the input. I have tried that print(data ['value'][0]['id']), but it not work for me. Because the value of groups is not the directly JSON file, I use groups = json.loads(groups.content) to get it. Now, I do not know how to get the value of the ID in the Response body.

Rahul K P · Accepted Answer · 2022-09-24 21:26:28Z

1

+100

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.

edited Sep 24, 2022 at 21:26

answered Sep 24, 2022 at 21:20

Rahul K P

16.2k4 gold badges40 silver badges56 bronze badges

Comments

musava_ribica · Accepted Answer · 2022-09-21 15:20:45Z

1

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.

answered Sep 21, 2022 at 15:20

musava_ribica

4821 gold badge8 silver badges21 bronze badges

Comments

Dinesh · Accepted Answer · 2022-09-25 14:12:38Z

1

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'])

answered Sep 25, 2022 at 14:12

Dinesh

1,0376 silver badges17 bronze badges

Comments

Mayuresh Choudhary · Accepted Answer · 2022-09-26 18:07:52Z

1

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)

answered Sep 26, 2022 at 18:07

Mayuresh Choudhary

113 bronze badges

Comments

hellbreak · Accepted Answer · 2022-09-27 10:30:02Z

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'])

Collectives™ on Stack Overflow

could not Extract Value from Json with python

7 Answers 7

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related