Search code examples
mysqljsonsphinx

MySQL flatten Json array to string of values

I have a JSON field in mysql for tags like so

[
    {"id": 138001, "tag": "Travel"}, 
    {"id": 138002, "tag": "Holidays"}
]

and I want to retrieve it as 'Travel Holidays'

I can get the values in the a json array format:

["Travel", "Holidays"]

with the current statement,

SELECT JSON_EXTRACT(post_tags, '$[*].tag') AS tags FROM posts WHERE post_id = 646745;

but I'm not sure if there is a way to flatten the array into a single string separated by spaces, is it possible?

My reason for doing this, is that sphinx search indexes the tags field also and you can get the id values in the index which isn't desired at all

Solution

  • Well you could just let sphinx index ["Travel", "Holidays"] directly.

    As per charset_table rules, the [, " and , will just be 'ignored' (more technically treated as separators and collapsed away)

    Also not familiar with it, but maybe JSON_UNQUOTE could work? Don't know if it can collapse arrays.