json_extract_array do not return result

[tualantin]

I've got json like below and trying to extract each key_name via json_extract_array but getting empty column vendor

query is

dataset = host_inventory

| fields application as aps

| alter vendor = json_extract_array(to_json_string(aps),"$.key_name")

[ 

 {

    "identifier": null,

    "install_date": "2021-01-12",

    "installed_for_sid": "global",

    "is_from_appstore": null,

    "key_name": "{DB1CD517-ED68-464C-A797-7C3D29B3CB27}",

    "manager_name": "windows",

    "application_name": "VNC Server 6.7.2",

    "raw_version": "6.7.2.42622",

    "uninstall_string": "MsiExec.exe /I{DB1CD517-ED68-464C-A797-7C3D29B3CB27}",

    "url_info_about": "https://www.realvnc.com",

    "url_update_info": "",

    "vendor": "RealVNC Ltd",

    "version": "6.7.2.42622",

    "win_installer": true

  },

  {

    "identifier": null,

    "install_date": "2024-08-14",

    "installed_for_sid": "global",

    "is_from_appstore": null,

    "key_name": "{EAD0DD81-531A-4ECA-84DB-87BDB40B15DA}",

    "manager_name": "windows",

    "application_name": "Cortex XDR 8.4.1.53273",

    "raw_version": "8.4.1.53273",

    "uninstall_string": "MsiExec.exe /X{EAD0DD81-531A-4ECA-84DB-87BDB40B15DA}",

    "url_info_about": "",

    "url_update_info": "",

    "vendor": "Palo Alto Networks, Inc.",

    "version": "8.4.1.53273",

    "win_installer": true

  }

]

[nsinghvirk · Accepted solution]

Hello @tualantin 

Thanks for reaching out on LiveCommunity!

Please try using below query.

dataset = host_inventory
| arrayexpand applications
| alter vendor = json_extract(applications,"$.key_name")
| filter (vendor not in (null, """"""))
| fields applications , vendor

Please click Accept as Solution to acknowledge that the answer to your question has been provided.