How would I query nested JSON data values in SQL using json_extract()?

Mysql version is 6.7+

Example of the column I need to get a value from below.

json_extract(goal_templates.template_data, group_concat('$.resources')) ->This results in a NULL return for all rows.

Template Data

{
    "Resolve housing issues": {
        "tasks": [{
            "name": "Use Decision Map to explore paths to resolving'' housing issue",
            "end_date": "15 days",
            "taskType": 3,
            "help_text": "",
            "resources": [],
            "start_date": "today",
            "actionOrder": "1",
            "recur_every": "",
            "resource_reference_id": ""
        }, {
            "name": "Select a local Debt & Credit Counseling Service",
            "end_date": "15 days",
            "taskType": 3,
            "help_text": "Add & tag local Credit & Debt Counseling Service (Organization)",
            "resources": ["14579", "14580"],
            "start_date": "today",
            "actionOrder": "2",
            "recur_every": "",
            "resource_reference_id": "14579, 14580"
        }, {
            "name": "[Schedule Credit & Debt Counseling as SGE or RGE]",
            "end_date": "15 days",
            "taskType": 3,
            "help_text": "",
            "resources": [],
            "start_date": "today",
            "actionOrder": "3",
            "recur_every": "",
            "resource_reference_id": ""
        }, {
            "name": "Rate resource for benefit of those who follow",
            "end_date": "15 days",
            "taskType": 3,
            "help_text": "",
            "resources": [],
            "start_date": "today",
            "actionOrder": "4",
            "recur_every": "",
            "resource_reference_id": ""
        }],
        "sequence_num": "1"
    }
}

Solution

We use JSON_EXTRACT to extract a key from the JSON_COLUMN using the following syntax:

JSON_EXTRACT(json_field, '$.key')

If, however, we need to extract nested keys like in your case, we can either append the nested child keys to the path like

JSON_EXTRACT('{"resolve_housing_issues": {"tasks": [{"name": "Decision Map", "end_date": "15 days"}]}}', '$.resolve_housing_issues.tasks[0].name')

as is depicted in @bill-karwin's answer or make use of the wildcards like the following:

SELECT JSON_EXTRACT('{"resolve_housing_issues": {"tasks": [{"name": "Decision Map", "end_date": "15 days"}]}}', '$**.resolve_housing_issues') as resolve_housing_issues;

It produces the following result:

While the query

SELECT JSON_EXTRACT('{"resolve_housing_issues": {"tasks": [{"name": "Decision Map", "end_date": "15 days"}]}}', '$**.tasks') as tasks;

produces the following result:

So on and so forth.