Join all JSON object string values-CodePudding

In a MySQL 8 JSON column I have a JSON object with values of different types (but with no nested objects). Like this:

{
    "abc": "Something123",
    "foo": 63.4,
    "bar": "Hi world!",
    "xyz": false
}

What is the simplest way to select the joined string values? As an example, from the above JSON we should get "Something123 Hi world!"?

CodePudding user response：

Here's a solution for MySQL 8.0:

select group_concat(json_unquote(json_extract(t.data, concat('$.', j.`key`))) separator '') as joined_string 
from mytable cross join json_table(json_keys(mytable.data), '$[*]' columns (`key` varchar(20) path '$')) j 
join mytable t on json_type(json_extract(t.data, concat('$.', j.`key`)))='STRING';

Output given your example data:

 ----------------------- 
| joined_string         |
 ----------------------- 
| Something123Hi world! |
 -----------------------

I wonder, however, if this is a good idea to store data in JSON if you need to do this. The solution is complex to develop, and would be hard to debug or modify.

Using JSON to store data as a document when you really want SQL predicates to treat the document's fields as discrete elements is harder than using normal rows and columns.

If you're using MySQL 5.7 or earlier, then the JSON_TABLE() function is not supported. In that case, I'd suggest fetching the whole JSON document into your application, and explode it into an object you can manipulate.

CodePudding user response：

Here is a solution:

SELECT GROUP_CONCAT(va SEPARATOR ' ') str_vals_only
FROM
(
    SELECT id, JSON_UNQUOTE(JSON_EXTRACT(my_json_col, CONCAT('$.', j.obj_key))) va
    FROM my_tbl,
    JSON_TABLE(JSON_KEYS(my_json_col), '$[*]' COLUMNS(obj_key TEXT PATH "$")) j
    WHERE JSON_TYPE(JSON_EXTRACT(my_json_col, CONCAT('$.', j.obj_key))) = 'STRING'
) a
GROUP BY id

Not very simple and concise, but I guess that's as good as it gets.

I love the JSON support in MySQL. For some specific cases the JSON type can provide a very nice and flexible solution. That's the main reason I don't even think about switching to MariaDB (which has a much more limited JSON support). But I wish there were more functions for JSON manipulation. The JSON_TABLE function is very powerful, but it can be a bit complicated and verbose.