Buy Percona ServicesBuy Now!

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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/...tract-sub-json
    Thanks in advance.
Working...
X