MySQL nested JSON column search and extract sub JSON from the nested JSON

[kanmani selvan]

I have a MySQL table authors with columns id, name and published_books. In this, published_books is a JSON column. With sample data,

id| name | published_books
-----------------------------------------------------------------------
1 | Tina | {"17e9bf8f": {"name": "Book 1", "tags": ["self Help", "Social"],"language": "English", "release_date": "2017-05-01"}, "8e8b2470": {"name": "Book 2", "tags": ["Inspirational"], "language": "English", "release_date": "2017-05-01"}}
-----------------------------------------------------------------------
2 | John | {"8e8b2470": { "name": "Book 4", "tags": ["Social"], "language": "Tamil", "release_date": "2017-05-01"}}
-----------------------------------------------------------------------
3 | Keith | {"17e9bf8f": { "name": "Book 5", "tags": ["Comedy"], "language": "French", "release_date": "2017-05-01" }, "8e8b2470": {"name": "Book 6", "tags": ["Social", "Life"], "language": "English", "release_date": "2017-05-01"}}
-----------------------------------------------------------------------

As you see, the published_books column has nested JSON data (one level). JSON will have dynamic UUIDs as the keys and its values will be book details as a JSON.

I want to search for books with certain conditions and extract those books JSON data alone to return as the result.

The query that I've written,

select JSON_EXTRACT(published_books, '$.*') from authors
where JSON_CONTAINS(published_books->'$.*.language', '"English"')
and JSON_CONTAINS(published_books->'$.*.tags', '["Social"]');


This query performs the search and returns the entire published_books JSON. But I wanted just those books JSON alone.

The expected result,

result
--------
{"17e9bf8f": {"name": "Book 1", "tags": ["self Help", "Social"],"language": "English", "release_date": "2017-05-01"}, "8e8b2470": {"name": "Book 6", "tags": ["Social", "Life"], "language": "English", "release_date": "2017-05-01"}

Please excuse the code format. For clear formatting, please refer https://stackoverflow.com/questions/47566765/mysql-nested-json-column-search-and-extract-sub-json

Thanks in advance.