Get JSON Parent object name in MySql 8

{
  "hi": {
    "blue_line_branch": {
      "stations": {
        "1_station_name": "यमुना बैंक",
        "2_station_name": "लक्समी नगर",
        "3_station_name": "निर्माण विहार",
        "4_station_name": "प्रीत विहार",
        "5_station_name": "कारकारडूमा",
        "5_notes_1": "",
        "5_notes_2": "",
        "6_station_name": "आनंद विहार िस्बत",
        "6_notes_1": "",
        "6_notes_2": "",
        "7_station_name": "कौशाम्बी",
        "8_station_name": "वैशाली"
      }
    },
    "rapid_metro": {
      "stations": {
        "1_station_name": "सेक्टर ५५-५६",
        "2_station_name": "सेक्टर-५४ चौक",
        "3_station_name": "सेक्टर-५३-५४",
        "4_station_name": "सेक्टर-४२-४३",
        "5_station_name": "फेज-१",
        "6_station_name": "सिकंदरपुर",
        "6_notes_1": "",
        "6_notes_2": "",
        "7_station_name": "फेज-२",
        "8_station_name": "बेलवेडेरे टावर्स",
        "9_station_name": "साइबर सिटी",
        "10_station_name": "मौलसरी ावेनुए",
        "11_station_name": "फेज-३"
      }
    }
  }
}

I have stored the above json string in a mysql (Version 8) table. How do I get the parent node of "stations" object which in this case be "blue_line_branch" and "rapid_metro". The actual json string is quite long.

Woud greatly appreciate if you could help.

Solution

select branches.branch
from mytable
cross join json_table(json_keys(json_extract(data, '$.hi')), '$[*]' columns (branch text path '$')) as branches
cross join json_table(json_keys(json_extract(data, concat('$.hi."', branch, '"'))), '$[*]' columns (`key` text path '$')) as `keys`
where `keys`.`key` = 'stations';

Result:

+------------------+
| branch           |
+------------------+
| rapid_metro      |
| blue_line_branch |
+------------------+

Demo fiddle: https://dbfiddle.uk/gXYSNhSq

You said the real JSON is much longer, so this solution may not be exactly what you need. But it works with the sample data you showed, and it demonstrates the general technique, which I admit is complex.

Storing JSON often makes queries harder, not easier.

It would be much simpler to store the data not using JSON but in normal rows and columns.