MySQL nested JSON column search and extract sub JSON from the nested JSON
Posted by: kanmani selvan
Date: November 30, 2017 05:16AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL nested JSON column search and extract sub JSON from the nested JSON
17869
November 30, 2017 05:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.