fetch value from a JSON output | python

Question

I have a JSON output as below, how can i fetch clientHostname,clientId,completionTime,creationTime,status,retentionTime,fileCount value from it in a python code in a formatted table format.

[{'attributes': [{'key': '*anchor save set time', 'values': ['158']}, {'key': '*backup start time', 'values': ['158790']}, {'key': '*policy action name', 'values': ['Server db backup: 158790']}, {'key': '*policy name', 'values': ['Server Protection: 158860']}, {'key': '*policy protection period', 'values': ['0: 1580']}, {'key': '*policy workflow name', 'values': ['Server backup: 15860']}, {'key': '*ss clone retention', 'values': [' 158760: 150: 186']}, {'key': 'group', 'values': ['Server Protection']}, {'key': 'saveset features', 'values': ['CLIENT_SAVETIME']}], 'browseTime': '2020-05-19T10:01:00+08:00', 'clientHostname': 'server.com', 'clientId': '52d79c1', 'completionTime': '2020-04-27T10:01:00+08:00', 'creationTime': '2020-04-21T10:01:00+08:00', 'fileCount': 2, 'id': '2b2d925f9c1', 'instances': [{'clone': False, 'id': '1587952860', 'status': 'Recoverable', 'volumeIds': ['444']}], 'level': '1', 'links': [{'href': 'https://server.com/backups/2b4c1', 'rel': 'item'}], 'name': 'index:b9c9dc1', 'retentionTime': '2020-05-18T23:59:59+08:00', 'saveTime': '2020-04-27T10:01:00+08:00', 'shortId': '1151745244', 'size': {'unit': 'Byte', 'value': 408}, 'type': 'File'}, {'attributes': [{'key': '*anchor save set time', 'values': ['1585706503']}, {'key': '*backup start time', 'values': ['1587952855']}, {'key': '*policy action name', 'values': ['Server db backup: 15855']}, {'key': '*policy name', 'values': ['Server Protection: 158795']}, {'key': '*policy protection period', 'values': ['0: 1585']}, {'key': '*policy workflow name', 'values': ['Server backup: 1587855']}, {'key': '*ss clone retention', 'values': [' 158755: 1587952855: 186']}, {'key': 'group', 'values': ['Server Protection']}, {'key': 'saveset features', 'values': ['CLIENT_SAVETIME']}], 'browseTime': '2020-05-18T10:00:55+08:00', 'clientHostname': 'server.com', 'clientId': '52d7ae9c1', 'completionTime': '2020-04-27T10:00:55+08:00', 'creationTime': '2020-04-27T10:00:55+08:00', 'fileCount': 2, 'id': '0b8ae9c1', 'instances': [{'clone': False, 'id': '1', 'status': 'Recoverable', 'volumeIds': ['446929344']}], 'level': '1', 'links': [{'href': 'https://server.com/backups/0b8a', 'rel': 'item'}], 'name': 'index:ec3df9c1', 'retentionTime': '2020-05-18T23:59:59+08:00', 'saveTime': '2020-04-27T10:00:55+08:00', 'shortId': '1168522455', 'size': {'unit': 'Byte', 'value': 408}, 'type': 'File'}]

This question may have been answered here: stackoverflow.com/questions/36862308/… — Charles Carriere
– Charles Carriere, Commented Apr 28, 2020 at 3:45
I tried below but getting error, could you please suggest. `data1 = data['attributes']['clientHostname'] print (data1)' getting below error- ' data1 = data['attributes']['clientHostname'] TypeError: list indices must be integers or slices, not str' — Nisha Budakoti
– Nisha Budakoti, Commented Apr 28, 2020 at 3:50
The value of the 'attributes' key is a list of dicts ( note the open square bracket '[' after 'attributes': ) - so you need to reference each item in the list by its position, not a key reference. try data1 = data['attributes'][8]['clientHostname'] (not sure I have counted right, but try playing with that index to get what you are looking for) — Charles Carriere
– Charles Carriere, Commented Apr 28, 2020 at 16:25

PRIN · Accepted Answer · 2020-04-28 03:51:38Z

0

You could do it like this (Just load your json data in a dataframe first):

`import pandas as pd`
`df = pd.DataFrame.from_records(x)`
`df[['clientHostname', 'clientId', 'completionTime', 'creationTime', 'retentionTime', 'fileCount']]`

Here x is your json data that you have provided.

answered Apr 28, 2020 at 3:51

PRIN

3441 silver badge9 bronze badges

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

10 Comments

Pygirl Over a year ago

You are missing status that's the main thing. Rest are easy to extract

Nisha Budakoti Over a year ago

It's giving the below output which is not correct- ` attributes ... type 0 [{'key': '*anchor save set time', 'values': ['... ... File 1 [{'key': '*anchor save set time', 'values': ['... ... File 2 [{'key': '*anchor save set time', 'values': ['... ... File 3 [{'key': '*anchor save set time', 'values': ['... ... File [4 rows x 17 columns]`

PRIN Over a year ago

Hi @NishaBudakoti, for your 'status' part, you could try something like this: df['status'] = df.apply(lambda row: row.instances[0]['status'], axis=1)

Nisha Budakoti Over a year ago

thanks @Priya...Status part is coming correctly but above code is not giving the correct output for clientHostname,clientId,completionTime,creationTime,status,retentionTime,fileCount, could you please suggest.

PRIN Over a year ago

Hi @NishaBudakoti, that code is working fine too, not sure what do you want to achieve. I am getting the following output: clientHostname clientId completionTime creationTime retentionTime fileCount 0 server.com 52d79c1 2020-04-27T10:01:00+08:00 2020-04-21T10:01:00+08:00 2020-05-18T23:59:59+08:00 2 1 server.com 52d7ae9c1 2020-04-27T10:00:55+08:00 2020-04-27T10:00:55+08:00 2020-05-18T23:59:59+08:00 2 Not sure how to add a screenshot here!

|

Pygirl · Accepted Answer · 2020-04-28 04:25:04Z

0

Try this:

from pandas.io.json import json_normalize
import ast

js = [{'attributes': [{'key': '*anchor save set time', 'values': ['158']}, {'key': '*backup start time', 'values': ['158790']}, {'key': '*policy action name', 'values': ['Server db backup: 158790']}, {'key': '*policy name', 'values': ['Server Protection: 158860']}, {'key': '*policy protection period', 'values': ['0: 1580']}, {'key': '*policy workflow name', 'values': ['Server backup: 15860']}, {'key': '*ss clone retention', 'values': ['   158760:      150:   186']}, {'key': 'group', 'values': ['Server Protection']}, {'key': 'saveset features', 'values': ['CLIENT_SAVETIME']}], 'browseTime': '2020-05-19T10:01:00+08:00', 'clientHostname': 'server.com', 'clientId': '52d79c1', 'completionTime': '2020-04-27T10:01:00+08:00', 'creationTime': '2020-04-21T10:01:00+08:00', 'fileCount': 2, 'id': '2b2d925f9c1', 'instances': [{'clone': False, 'id': '1587952860', 'status': 'Recoverable', 'volumeIds': ['444']}], 'level': '1', 'links': [{'href': 'https://server.com/backups/2b4c1', 'rel': 'item'}], 'name': 'index:b9c9dc1', 'retentionTime': '2020-05-18T23:59:59+08:00', 'saveTime': '2020-04-27T10:01:00+08:00', 'shortId': '1151745244', 'size': {'unit': 'Byte', 'value': 408}, 'type': 'File'}, {'attributes': [{'key': '*anchor save set time', 'values': ['1585706503']}, {'key': '*backup start time', 'values': ['1587952855']}, {'key': '*policy action name', 'values': ['Server db backup: 15855']}, {'key': '*policy name', 'values': ['Server Protection: 158795']}, {'key': '*policy protection period', 'values': ['0: 1585']}, {'key': '*policy workflow name', 'values': ['Server backup: 1587855']}, {'key': '*ss clone retention', 'values': ['          158755:     1587952855:   186']}, {'key': 'group', 'values': ['Server Protection']}, {'key': 'saveset features', 'values': ['CLIENT_SAVETIME']}], 'browseTime': '2020-05-18T10:00:55+08:00', 'clientHostname': 'server.com', 'clientId': '52d7ae9c1', 'completionTime': '2020-04-27T10:00:55+08:00', 'creationTime': '2020-04-27T10:00:55+08:00', 'fileCount': 2, 'id': '0b8ae9c1', 'instances': [{'clone': False, 'id': '1', 'status': 'Recoverable', 'volumeIds': ['446929344']}], 'level': '1', 'links': [{'href': 'https://server.com/backups/0b8a', 'rel': 'item'}], 'name': 'index:ec3df9c1', 'retentionTime': '2020-05-18T23:59:59+08:00', 'saveTime': '2020-04-27T10:00:55+08:00', 'shortId': '1168522455', 'size': {'unit': 'Byte', 'value': 408}, 'type': 'File'}]

df = json_normalize(js)
df[['clone','id','status','volumeIds']] = df.instances.apply(lambda x: pd.Series(ast.literal_eval(str(x[0]))))

df:

attributes  browseTime  clientHostname  clientId    completionTime  creationTime    fileCount   id  instances   level   ... name    retentionTime   saveTime    shortId type    size.unit   size.value  clone   status  volumeIds
0   [{'key': '*anchor save set time', 'values': ['...   2020-05-19T10:01:00+08:00   server.com  52d79c1 2020-04-27T10:01:00+08:00   2020-04-21T10:01:00+08:00   2   1587952860  [{'clone': False, 'id': '1587952860', 'status'...   1   ... index:b9c9dc1   2020-05-18T23:59:59+08:00   2020-04-27T10:01:00+08:00   1151745244  File    Byte    408 False   Recoverable [444]
1   [{'key': '*anchor save set time', 'values': ['...   2020-05-18T10:00:55+08:00   server.com  52d7ae9c1   2020-04-27T10:00:55+08:00   2020-04-27T10:00:55+08:00   2   1   [{'clone': False, 'id': '1', 'status': 'Recove...   1   ... index:ec3df9c1  2020-05-18T23:59:59+08:00   2020-04-27T10:00:55+08:00   1168522455  File    Byte    408 False   Recoverable [446929344]

df[['clientHostname','clientId','completionTime','creationTime','status','retentionTime','fileCount']]

clientHostname  clientId    completionTime              creationTime                status     retentionTime                fileCount
0   server.com  52d79c1     2020-04-27T10:01:00+08:00   2020-04-21T10:01:00+08:00   Recoverable 2020-05-18T23:59:59+08:00   2
1   server.com  52d7ae9c1   2020-04-27T10:00:55+08:00   2020-04-27T10:00:55+08:00   Recoverable 2020-05-18T23:59:59+08:00   2

answered Apr 28, 2020 at 4:25

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

6 Comments

Nisha Budakoti Over a year ago

Thanks @Pygirl.. I am running the same code but getting the below output. ` attributes ... volumeIds 0 [{'key': '*anchor save set time', 'values': ['... ... [444] 1 [{'key': '*anchor save set time', 'values': ['... ... [446929344] [2 rows x 21 columns]`

Pygirl Over a year ago

You will get that only. 2 rows × 21 columns . Then select the columns you want to have df[['clientHostname','clientId','completionTime','creationTime','status','retentionTime','fileCount']]

Nisha Budakoti Over a year ago

do I have to import pandas as pd because in the above code it is giving pd is not defined, Also if i define it also getting the same output for df ` attributes ... volumeIds 0 [{'key': '*anchor save set time', 'values': ['... ... [444] 1 [{'key': '*anchor save set time', 'values': ['... ... [446929344] [2 rows x 21 columns]`

Pygirl Over a year ago

screenshot please. We can't guess what you are doing.

Nisha Budakoti Over a year ago

How can i add screenshot?

|

Collectives™ on Stack Overflow

fetch value from a JSON output | python

2 Answers 2

10 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related