Parse nested Json to splunk query which has string

I have a multiple result for a macAddress which contains the device details.

This is the sample data

    "data": {
        "a1:b2:c3:d4:11:22": {
            "deviceIcons": {
                "type": "Phone",
                "icons": {
                    "3x": null,
                    "2x": "image.png"
                }
            },
            "advancedDeviceId": {
                "agentId": 113,
                "partnerAgentId": "131",
                "dhcpHostname": "Galaxy-J7",
                "mac": "a1:b2:c3:d4:11:22",
                "lastSeen": 12,
                "model": "Android Phoe",
                "id": 1
            }
        },
        "a0:b2:c3:d4:11:22": {
            "deviceIcons": {
                "type": "Phone",
                "icons": {
                    "3x": null,
                    "2x": "image.png"
                }
            },
            "advancedDeviceId": {
                "agentId": 113,
                "partnerAgentId": "131",
                "dhcpHostname": "Galaxy",
                "mac": "a0:b2:c3:d4:11:22",
                "lastSeen": 12,
                "model": "Android Phoe",
                "id": 1
            }
        }
    }
}

How can I query in splunk for all the kind of above sample results to get the advancedDeviceId.model and advancedDeviceId.id in tabular format?

I think this will do what you want

| spath
| untable _time column value
| rex field=column "data.(?<address>[^.]+)\.advancedDeviceId\.(?<item>[^.]+)"
| table _time address item value
| eval {item}=value
| stats list(model) as model
        list(id) as id
        list(dhcpHostname) as dhcpHostname
        list(mac) as mac
        by address

Here is a "run anywhere" example that has two events each with two addresses:

| makeresults
| eval _raw="{\"data\":{\"a1:b2:c3:d4:11:21\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Galaxy-J7\",\"mac\":\"a1:b2:c3:d4:11:21\",\"lastSeen\":12,\"model\":\"Android Phoe\",\"id\":1}},\"a0:b2:c3:d4:11:22\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"iPhone 6\",\"mac\":\"a0:b2:c3:d4:11:22\",\"lastSeen\":12,\"model\":\"Apple Phoe\",\"id\":2}}}}"
| append [
    | makeresults
    | eval _raw="{\"data\":{\"b1:b2:c3:d4:11:23\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Nokia\",\"mac\":\"b1:b2:c3:d4:11:23\",\"lastSeen\":12,\"model\":\"Symbian Phoe\",\"id\":3}},\"b0:b2:c3:d4:11:24\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Windows\",\"mac\":\"b0:b2:c3:d4:11:24\",\"lastSeen\":12,\"model\":\"Windows Phoe\",\"id\":4}}}}"
]
| spath
| untable _time column value
| rex field=column "data.(?<address>[^.]+)\.advancedDeviceId\.(?<item>[^.]+)"
| table _time address item value
| eval {item}=value
| stats list(model) as model
        list(id) as id
        list(dhcpHostname) as dhcpHostname
        list(mac) as mac
        by address