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

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
– Willy, Commented Dec 31, 2020 at 19:54

Willy · Accepted Answer · 2021-01-01 13:49:15Z

4

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

answered Jan 1, 2021 at 13:49

Willy

931 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Junaid Farooq · Accepted Answer · 2021-01-01 07:04:35Z

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.

3 Comments

Willy Over a year ago

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 Over a year ago

I tried something like this and MySQL workbench didn't like the expression. json_extract(template_data, '$[*].tasks[*].resources')

Bill Karwin Over a year ago

Probably something involving 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.

Your Common Sense · Accepted Answer · 2025-07-22 09:55:40Z

0

Incidentally, I was looking for a generic answer to "How to query nested JSON objects" question, while all answers here are too focused on one specific non-trivial case.

So here it goes, just a dot notation:

JSON_VALUE(json_column,'$.amount.value') as value

In hindsight, it looks quite obvious, but well, here we are.

If JSON_VALUE() is not available you may try this

JSON_UNQUOTE(JSON_EXTRACT(json_column,'$.amount.value') as value

Also, you may cast the extracted value to desired type

CAST(JSON_VALUE(json_column,'$.amount.value') as DECIMAL) as value

edited Jul 22 at 9:55

answered Jul 22 at 9:08

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related