Extracting values from deeply nested JSON structures

Question

This is a structure I'm getting from elsewhere, that is, a list of deeply nested dictionaries:

{
    "foo_code": 404,
    "foo_rbody": {
        "query": {
            "info": {
                "acme_no": "444444",
                "road_runner": "123"
            },
            "error": "no_lunch",
            "message": "runner problem."
        }
    },
    "acme_no": "444444",
    "road_runner": "123",
    "xyzzy_code": 200,
    "xyzzy_rbody": {
        "api": {
            "items": [
                {
                    "desc": "OK",
                    "id": 198,
                    "acme_no": "789",
                    "road_runner": "123",
                    "params": {
                        "bicycle": "2wheel",
                        "willie": "hungry",
                        "height": "1",
                        "coyote_id": "1511111"
                    },
                    "activity": "TRAP",
                    "state": "active",
                    "status": 200,
                    "type": "chase"
                }
            ]
        }
    }
}

{
    "foo_code": 200,
    "foo_rbody": {
        "query": {
            "result": {
                "acme_no": "260060730303258",
                "road_runner": "123",
                "abyss": "26843545600"
            }
        }
    },
    "acme_no": "260060730303258",
    "road_runner": "123",
    "xyzzy_code": 200,
    "xyzzy_rbody": {
        "api": {
            "items": [
                {
                    "desc": "OK",
                    "id": 198,
                    "acme_no": "789",
                    "road_runner": "123",
                    "params": {
                        "bicycle": "2wheel",
                        "willie": "hungry",
                        "height": "1",
                        "coyote_id": "1511111"
                    },
                    "activity": "TRAP",
                    "state": "active",
                    "status": 200,
                    "type": "chase"
                }
            ]
        }
    }
}

Asking for different structures is out of question (legacy apis etc).

So I'm wondering if there's some clever way of extracting selected values from such a structure.

The candidates I was thinking of:

flatten particular dictionaries, building composite keys, smth like:

{ "foo_rbody.query.info.acme_no": "444444", "foo_rbody.query.info.road_runner": "123", ... }

Pro: getting every value with one access and if predictable key is not there, it means that the structure was not there (as you might have noticed, dictionaries may have different structures depending on whether it was successful operation, error happened, etc).

Con: what to do with lists?

Use some recursive function that would do successive key lookups, say by "foo_rbody", then by "query", "info", etc.

Any better candidates?

score 9 · Accepted Answer · answered Aug 06 '13 at 21:09

9

You can try this rather trivial function to access nested properties:

import re

def get_path(dct, path):
    for i, p in re.findall(r'(\d+)|(\w+)', path):
        dct = dct[p or int(i)]
    return dct

Usage:

value = get_path(data, "xyzzy_rbody.api.items[0].params.bicycle")

answered Aug 06 '13 at 21:09

georg

211,518
52
313
390

1

Simple, nonrecursive, tight. Why didn't I think about smth like that? Oh well. – LetMeSOThat4U Aug 07 '13 at 08:58

score 2 · Answer 2 · edited May 23 '17 at 10:26

2

Maybe the function byPath in my answer to this post might help you.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 06 '13 at 20:16

Hyperboreus

31,997
9
47
87

score 1 · Answer 3 · answered Aug 06 '13 at 19:58

You could create your own path mechanism and then query the complicated dict with paths. Example:

/ : get the root object
/key: get the value of root_object['key'], e.g. /foo_code --> 404
/key/key: nesting: /foo_rbody/query/info/acme_no -> 444444
/key[i]: get ith element of that list, e.g. /xyzzy_rbody/api/items[0]/desc --> "OK"

The path can also return a dict which you then run more queries on, etc.

It would be fairly easy to implement recursively.

score 1 · Answer 4 · edited May 23 '17 at 12:29

1

I think about two more solutions:

You can try package Pynq, described here - structured query language for JSON (in Python). As far as a I understand, it's some kind of LINQ for python.

You may also try to convert your JSON to XML and then use Xquery language to get data from it - XQuery library under Python

edited May 23 '17 at 12:29

Community

1
1

answered Aug 06 '13 at 20:23

Roman Pekar

107,110
28
195
197

pynq is cool, thanks! I'm not sure I will use it (it seems an overkill here) but it's very nice to have such tool. – LetMeSOThat4U Aug 07 '13 at 08:52

Extracting values from deeply nested JSON structures

4 Answers4

Linked