0

Problem: how to write a sqlite statement to select a value from a nested json object when the needed name is dynamic / variable. It is also important that this can be done from a single sql statement. Eventually, this will be executed from within a bash script.

In the object sample below, I need to list all the dot11.advertisedssid.ssid in the sql database. An acceptable solution is to list all values of dot11.advertisedssid.ssid that exist in the json object, but I would like to understand how to query a dynamic json name (so I can get the other nested values). In general I am using json_extract in my sql statement I just can’t figure out how to get to the ssid value (in this example)!

How do I know 733545801 is the field name and how can I then use it in the json_extract statement? And do that for all such nested objects.

Examples:

In general this is how I am querying other json values.

select json_extract(devices.device,'$."dot11.device"."dot11.device.typeset"') from devices;

An object sample from the database:

 "dot11.device": {
    "dot11.device.typeset": 257,
    "dot11.device.client_map": {

    },
    "dot11.device.num_client_aps": 0,
    "dot11.device.advertised_ssid_map": {
      "733545801": {
        "dot11.advertisedssid.ssid": "SampleFES-WiFi",
        "dot11.advertisedssid.ssidlen": 15,
        "dot11.advertisedssid.beacon": 1,
        "dot11.advertisedssid.probe_response": 1,
        "dot11.advertisedssid.channel": "6",
        "dot11.advertisedssid.ht_mode": "HT20",
        "dot11.advertisedssid.ht_center_1": 0,
        "dot11.advertisedssid.ht_center_2": 0,
        "dot11.advertisedssid.first_time": 1559567379,
        "dot11.advertisedssid.last_time": 1559567379,
        "dot11.advertisedssid.beacon_info": "",
        "dot11.advertisedssid.cloaked": 0,
        "dot11.advertisedssid.crypt_set": 268436162,
        "dot11.advertisedssid.maxrate": 65.000000,
        "dot11.advertisedssid.beaconrate": 10,
        "dot11.advertisedssid.beacons_sec": 2,
        "dot11.advertisedssid.ietag_checksum": 1220416683,
        "dot11.advertisedssid.wpa_mfp_required": 0,
        "dot11.advertisedssid.wpa_mfp_supported": 0,
        "dot11.advertisedssid.dot11d_country": "",
        "dot11.advertisedssid.dot11d_list": [
        ],
        "dot11.advertisedssid.wps_state": 0,
        "dot11.advertisedssid.dot11r_mobility": 0,
        "dot11.advertisedssid.dot11r_mobility_domain_id": 0,
        "dot11.advertisedssid.dot11e_qbss": 0,
        "dot11.advertisedssid.dot11e_qbss_stations": 0,
        "dot11.advertisedssid.dot11e_channel_utilization_perc": 0.000000,
        "dot11.advertisedssid.ccx_txpower": 0,
        "dot11.advertisedssid.cisco_client_mfp": 0,
        "dot11.advertisedssid.ie_tag_list": [
          0.000000,
          1.000000,
          3.000000,
          5.000000,
          42.000000,
          50.000000,
          48.000000,
          45.000000,
          61.000000,
          127.000000,
          221.000000
        ]
      }
    }

Thanks for the help!

PS. This is from the new kismet database and the redesigned schema.

Here is the whole object:

   {
  "kismet.device.base.manuf": "Texas Instruments",
  "kismet.device.base.key": "4202770D00000000_AFB4F569D2380000",
  "kismet.device.base.macaddr": "38:D2:69:F5:B4:AF",
  "kismet.device.base.phyname": "IEEE802.11",
  "kismet.device.base.phyid": 0,
  "kismet.device.base.name": "LincolnFES-WiFi",
  "kismet.device.base.commonname": "LincolnFES-WiFi",
  "kismet.device.base.type": "Wi-Fi AP",
  "kismet.device.base.basic_type_set": 1,
  "kismet.device.base.crypt": "WPA2-PSK",
  "kismet.device.base.basic_crypt_set": 2,
  "kismet.device.base.first_time": 1559567379,
  "kismet.device.base.last_time": 1559567379,
  "kismet.device.base.mod_time": 1559567380,
  "kismet.device.base.packets.total": 3,
  "kismet.device.base.packets.rx": 0,
  "kismet.device.base.packets.tx": 0,
  "kismet.device.base.packets.llc": 3,
  "kismet.device.base.packets.error": 0,
  "kismet.device.base.packets.data": 0,
  "kismet.device.base.packets.crypt": 0,
  "kismet.device.base.packets.filtered": 0,
  "kismet.device.base.datasize": 0,
  "kismet.device.base.packets.rrd": {
    "kismet.common.rrd.last_time": 1559567383,
    "kismet.common.rrd.minute_vec": [
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      1,
      2,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0
    ],
    "kismet.common.rrd.blank_val": 0,
    "kismet.common.rrd.aggregator": "default",
    "kismet.common.rrd.hour_vec": [
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0
    ],
    "kismet.common.rrd.day_vec": [
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0,
      0
    ]
  },
  "kismet.device.base.signal": {
    "kismet.common.signal.type": "dbm",
    "kismet.common.signal.last_signal": -56,
    "kismet.common.signal.last_noise": 0,
    "kismet.common.signal.min_signal": -74,
    "kismet.common.signal.min_noise": 0,
    "kismet.common.signal.max_signal": -56,
    "kismet.common.signal.max_noise": 0,
    "kismet.common.signal.maxseenrate": 10,
    "kismet.common.signal.encodingset": 1,
    "kismet.common.signal.carrierset": 1,
    "kismet.common.signal.signal_rrd": {
      "kismet.common.rrd.last_time": 1559567383,
      "kismet.common.rrd.minute_vec": [
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0
      ],
      "kismet.common.rrd.blank_val": 0,
      "kismet.common.rrd.aggregator": "peak_signal"
    }
  },
  "kismet.device.base.freq_khz_map": {
    "2437000.000000": 1,
    "2442000.000000": 1,
    "5500000.000000": 1
  },
  "kismet.device.base.channel": "6",
  "kismet.device.base.frequency": 2442000,
  "kismet.device.base.num_alerts": 0,
  "kismet.device.base.tags": {

  },
  "kismet.device.base.seenby": {
    "-1970862229": {
      "kismet.common.seenby.uuid": "5FE308BD-0000-0000-0000-00C0CAA60413",
      "kismet.common.seenby.first_time": 1559567379,
      "kismet.common.seenby.last_time": 1559567379,
      "kismet.common.seenby.num_packets": 3,
      "kismet.common.seenby.freq_khz_map": {
        "2437000.000000": 1,
        "2442000.000000": 1,
        "5500000.000000": 1
      },
      "kismet.common.seenby.signal": {
        "kismet.common.signal.type": "dbm",
        "kismet.common.signal.last_signal": -56,
        "kismet.common.signal.last_noise": 0,
        "kismet.common.signal.min_signal": -74,
        "kismet.common.signal.min_noise": 0,
        "kismet.common.signal.max_signal": -56,
        "kismet.common.signal.max_noise": 0,
        "kismet.common.signal.maxseenrate": 10,
        "kismet.common.signal.encodingset": 1,
        "kismet.common.signal.carrierset": 1,
        "kismet.common.signal.signal_rrd": {
          "kismet.common.rrd.last_time": 1559567383,
          "kismet.common.rrd.minute_vec": [
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0
          ],
          "kismet.common.rrd.blank_val": 0,
          "kismet.common.rrd.aggregator": "peak_signal"
        }
      }
    }
  },
  "kismet.device.base.server_uuid": "A8F71A2C-85F8-11E9-BA41-4B49534D4554",
  "dot11.device": {
    "dot11.device.typeset": 257,
    "dot11.device.client_map": {

    },
    "dot11.device.num_client_aps": 0,
    "dot11.device.advertised_ssid_map": {
      "733545801": {
        "dot11.advertisedssid.ssid": "LincolnFES-WiFi",
        "dot11.advertisedssid.ssidlen": 15,
        "dot11.advertisedssid.beacon": 1,
        "dot11.advertisedssid.probe_response": 1,
        "dot11.advertisedssid.channel": "6",
        "dot11.advertisedssid.ht_mode": "HT20",
        "dot11.advertisedssid.ht_center_1": 0,
        "dot11.advertisedssid.ht_center_2": 0,
        "dot11.advertisedssid.first_time": 1559567379,
        "dot11.advertisedssid.last_time": 1559567379,
        "dot11.advertisedssid.beacon_info": "",
        "dot11.advertisedssid.cloaked": 0,
        "dot11.advertisedssid.crypt_set": 268436162,
        "dot11.advertisedssid.maxrate": 65,
        "dot11.advertisedssid.beaconrate": 10,
        "dot11.advertisedssid.beacons_sec": 2,
        "dot11.advertisedssid.ietag_checksum": 1220416683,
        "dot11.advertisedssid.wpa_mfp_required": 0,
        "dot11.advertisedssid.wpa_mfp_supported": 0,
        "dot11.advertisedssid.dot11d_country": "",
        "dot11.advertisedssid.dot11d_list": [

        ],
        "dot11.advertisedssid.wps_state": 0,
        "dot11.advertisedssid.dot11r_mobility": 0,
        "dot11.advertisedssid.dot11r_mobility_domain_id": 0,
        "dot11.advertisedssid.dot11e_qbss": 0,
        "dot11.advertisedssid.dot11e_qbss_stations": 0,
        "dot11.advertisedssid.dot11e_channel_utilization_perc": 0,
        "dot11.advertisedssid.ccx_txpower": 0,
        "dot11.advertisedssid.cisco_client_mfp": 0,
        "dot11.advertisedssid.ie_tag_list": [
          0,
          1,
          3,
          5,
          42,
          50,
          48,
          45,
          61,
          127,
          221
        ]
      }
    },
    "dot11.device.num_advertised_ssids": 1,
    "dot11.device.probed_ssid_map": {

    },
    "dot11.device.num_probed_ssids": 0,
    "dot11.device.associated_client_map": {

    },
    "dot11.device.num_associated_clients": 0,
    "dot11.device.client_disconnects": 0,
    "dot11.device.last_sequence": 0,
    "dot11.device.bss_timestamp": 0,
    "dot11.device.num_fragments": 0,
    "dot11.device.num_retries": 0,
    "dot11.device.datasize": 0,
    "dot11.device.datasize_retry": 0,
    "dot11.device.last_probed_ssid_csum": 0,
    "dot11.device.last_beaconed_ssid": "LincolnFES-WiFi",
    "dot11.device.last_beaconed_ssid_checksum": 733545801,
    "dot11.device.last_bssid": "38:D2:69:F5:B4:AF",
    "dot11.device.last_beacon_timestamp": 1559567379,
    "dot11.device.wps_m3_count": 0,
    "dot11.device.wps_m3_last": 0,
    "dot11.device.wpa_handshake_list": [

    ],
    "dot11.device.wpa_nonce_list": [

    ],
    "dot11.device.wpa_anonce_list": [

    ],
    "dot11.device.wpa_present_handshake": 0,
    "dot11.device.min_tx_power": 0,
    "dot11.device.max_tx_power": 0,
    "dot11.device.supported_channels": [

    ],
    "dot11.device.link_measurement_capable": 0,
    "dot11.device.neighbor_report_capable": 0,
    "dot11.device.extended_capabilities": [

    ],
    "dot11.device.beacon_fingerprint": 4212996422,
    "dot11.device.probe_fingerprint": 0,
    "dot11.device.response_fingerprint": 0
  }
}
Improve this question
2
  • Your sample isn't valid json...
    – Shawn
    Commented Jun 8, 2019 at 13:33
  • Yes, this a subset of the larger object. Here is the whole object.
    – C0ntr07
    Commented Jun 8, 2019 at 13:38

2 Answers 2

Reset to default
1

When you want to recursively walk through the fields of an entire object and its contents, you need json_tree():

SELECT j.value
FROM devices AS d
JOIN json_tree(d.device) AS j
WHERE j.key = 'dot11.advertisedssid.ssid';

gives

value         
--------------
SampleFES-WiFi

when run on a table holding a fixed version of that sample object.

Improve this answer
4
  • Thank you very much. That works (of course). It’s also simple! And to think I wasted about two weeks of free time trying to figure this out!
    – C0ntr07
    Commented Jun 8, 2019 at 15:07
  • Is it possible to select more than one field at a time with json_tree in this solution? Say I wanted the ssid and kismet.device.base.key?
    – C0ntr07
    Commented Jun 8, 2019 at 21:40
  • @C0ntr07 use OR or IN?
    – Shawn
    Commented Jun 8, 2019 at 22:01
  • Thank you. I tried that but my data is in one column because (I think) I only have one select. But I don’t know how to fix that when using the json tree. Should I add a second item in the select statement?
    – C0ntr07
    Commented Jun 8, 2019 at 22:21
0

I know this is a bit old, but OP seemed (in comments) to want a more complete solution. I know I did when I first came across this answer. The accepted solution allows you to pull in one field from the JSON blob, but the common use case in OP's example is to pull multiple fields from that blob. After some searching I found that the json_extract() function works very well for this once you realize that the "dot11.device.advertised_ssid_map" object is an array. Once you provide it with an index his normal query method works.

Considerations:

  • OP's example is relating to the Kismet device field in the devices table, so my example will use a common query that I often need in the context of that table
  • With Kismet the keys used in these JSON blobs are long and contain dots, so the syntax for specifying them in SQLite3 is a bit cumbersome for some nested values
  • SQLite3's JSON1 extension does not seem to like some of the wildcarding syntax normally allowed in JSONPath specifications, so long explicit paths are required

So here is my solution:

SELECT devmac, strongest_signal, 
  json_extract(d.device, '$."dot11.device"."dot11.device.advertised_ssid_map"[0]."dot11.advertisedssid.ssid"') AS ssid,
  json_extract(d.device, '$."dot11.device"."dot11.device.advertised_ssid_map"[0]."dot11.advertisedssid.cloaked"') AS cloaked,
  json_extract(d.device, '$."kismet.device.base.signal"."kismet.common.signal.min_signal"') AS weakest_signal,
  json_extract(d.device, '$."kismet.device.base.channel"') AS channel,
  json_extract(d.device, '$."dot11.device"."dot11.device.num_associated_clients"') AS clientCnt,
  json_extract(d.device, '$."kismet.device.base.crypt"') AS crypt,
  json_extract(d.device, '$."kismet.device.base.manuf"') AS manuf
FROM devices AS d
WHERE type = 'Wi-Fi AP'
;
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.