1

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"}}
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Willy
  • 53
  • 1
  • 6
  • Ignore the group_concat() as I can see how that wouldn't work. I still don't understand how I would retrieve the $.resources values only? – Willy Dec 31 '20 at 19:54

3 Answers3

2

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

Willy
  • 53
  • 1
  • 6
1

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:

Extracing the value of child key

While the query

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

Extracting the value of sub-child key

So on and so forth.

More on this can be found here.

Junaid Farooq
  • 82
  • 13
0
mysql> select json_extract(template_data, '$."Resolve housing issues".tasks[*].resources') as resources from goal_templates;
+----------------------------------+
| resources                        |
+----------------------------------+
| [[], ["14579", "14580"], [], []] |
+----------------------------------+
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That seems to work wonderfully! Thank you Bill. I have a follow-up question though. What would the syntax look like if instead of specifying "Resolve housing issues" object, I wanted to get the same result for all of the rows in the table? – Willy Dec 31 '20 at 20:49
  • I tried something like this and MySQL workbench didn't like the expression. ```json_extract(template_data, '$[*].tasks[*].resources')``` – Willy Dec 31 '20 at 20:51
  • Probably something involving [JSON_ARRAYAGG()](https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg). But let me make a suggestion: if you don't know how to code expressions with JSON data on your own, you should really consider not storing data in JSON format. Use normal columns and rows. – Bill Karwin Dec 31 '20 at 20:56