Как написать SQL-запрос для извлечения значения из вложенного объекта json, идентифицируемого именем поля переменной
Проблема: как написать sqlite оператор для выбора значения из вложенного json объект, когда нужное имя является динамическим / переменным. Также важно, что это можно сделать из одного sql заявление. В конце концов, это будет выполнено изнутри bash скрипт.
В приведенном ниже примере объекта мне нужно перечислить все dot11.advertisedssid.ssid в sql база данных. Приемлемым решением является перечисление всех значений dot11.advertisedssid.ssid которые существуют в json объект, но я хотел бы понять, как запросить динамический json имя (чтобы я мог получить другие вложенные значения). В общем пользуюсь json_extract в моем sql Утверждение Я просто не могу понять, как получить значение ssid (в этом примере)!
Откуда мне знать 733545801 это имя поля и как я могу использовать его в json_extract заявление? И сделать это для всех таких вложенных объектов.
Примеры:
В общем, вот как я запрашиваю другие значения JSON.
select json_extract(devices.device,'$."dot11.device"."dot11.device.typeset"') from devices;
Пример объекта из базы данных:
"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
]
}
}
Спасибо за помощь!
PS. Это из нового kismet база данных и переработанная схема.
Вот весь объект:
{
"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
}
}
2 ответа
Решение
Если вы хотите рекурсивно пройтись по полям всего объекта и его содержимого, вам нужно json_tree ():
SELECT j.value
FROM devices AS d
JOIN json_tree(d.device) AS j
WHERE j.key = 'dot11.advertisedssid.ssid';
дает
value
--------------
SampleFES-WiFi
при запуске на столе, содержащем фиксированную версию этого образца объекта.
Я знаю, что это немного устарело, но OP, похоже (в комментариях), хочет более полное решение. Я знал, что знал, когда впервые наткнулся на этот ответ. Принятое решение позволяет вам извлекать одно поле из большого двоичного объекта JSON, но общий вариант использования в примере OP - извлечение нескольких полей из этого большого двоичного объекта. После некоторых поисков я обнаружил, что
json_extract() функция работает очень хорошо для этого, если вы понимаете, что
"dot11.device.advertised_ssid_map"объект - это массив. Как только вы предоставите ему индекс, его обычный метод запроса будет работать.
Соображения:
- Пример OP относится к полю устройства Kismet в таблице устройств, поэтому в моем примере будет использоваться общий запрос, который мне часто нужен в контексте этой таблицы.
- С Kismet ключи, используемые в этих JSON blobs, длинные и содержат точки, поэтому синтаксис для их указания в SQLite3 немного громоздок для некоторых вложенных значений.
- Расширению SQLite3 JSON1, похоже, не нравится некоторый синтаксис подстановочных знаков, обычно разрешенный в спецификациях JSONPath, поэтому требуются длинные явные пути
Итак, вот мое решение:
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'
;