Join all JSON object string values

Question

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!"?

score 0 · Answer 1 · answered Oct 28 '21 at 22:12

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.

Bill, thanks for your answer. Unfortunately, your solution is very slow. On a table with 10k rows it ran for 10 minutes and I had to interrupt it. See my answer - it uses similar JSON functions, but took 0.1 sec on the same table. — at54321, Oct 29 '21 at 05:35
As for "I wonder, however, if this is a good idea to store data in JSON if you need to do this": Yes, for my case JSON works great. Don't draw conclusions just from one odd query I would occasionally need to run. :) — at54321, Oct 29 '21 at 05:37
Fair enough. It's true that any kind of denormalization is an advantage for certain queries, and if those are the queries you need to optimize the majority of the time, then it's a net win. But every type of optimization incurs a penalty for other types of queries. — Bill Karwin, Oct 29 '21 at 18:04

at54321 · Answer 2 · 2021-10-29T07:08:22.957

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.

Join all JSON object string values

2 Answers2