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

Question

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"}}

Answer 1

The selector I was looking for seems to be a "$**" wildcard. This allowed me to grab all of the unnamed objects for each of the rows and the values of the nested resources.

JSON_EXTRACT(template_data, '$**.tasks[*].resources') AS resources

Answer 2

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.

More on this can be found here.

Answer 3

mysql> select json_extract(template_data, '$."Resolve housing issues".tasks[*].resources') as resources from goal_templates;
+----------------------------------+
| resources                        |
+----------------------------------+
| [[], ["14579", "14580"], [], []] |
+----------------------------------+