MariaDB/MySQL select query replace json array of ids into concatenated values

Question

I'm not too familiar with database queries, but I would like to do the following: in a table, I have stored a column of tag ids like: [1,6,8] and I wish to return "Music, Dance, Pop" for example in my select query.

I did not design the database schema, and I'm not allowed to modify it.

The reason I would like to do so is the response of this query would be directly sent to the client for table rendering, and with the current ORM retrieval (PHP Laravel), this takes a long time retrieving the same related model for every row. So I would like to know if a query like this would be possible.

My schema looks something like this:

posts

id | name | ... | tag_ids (JSON array) | ...

tags

id | name

I have thought of using JSON_REPLACE, but have no idea on how to approach this problem at all. Is this even possible in plain SQL without code to post process the results?

You need to use a linking table for the tags. That would make things alot easier.Like: Genres (id, name) and Tags (genre_id, tag) — Alphastrick
– Alphastrick, Commented Aug 16, 2019 at 4:52
@Alphastrick Well, personally I wouldn't design the tables like this, but well, this exists and I had to work around it... — Daniel Cheung
– Daniel Cheung, Commented Aug 16, 2019 at 4:54
@Nick I'm seeing this: 5.5.5-10.3.16-MariaDB-log as the version — Daniel Cheung
– Daniel Cheung, Commented Aug 16, 2019 at 5:01

Tim Biegeleisen · Accepted Answer · 2019-08-16 05:20:51Z

1

and I'm not allowed to modify it

Ideally, you might to consider normalizing your data, which would make it easier to handle this problem. That being said, with the help of the JSON functions we can try a join combined with an aggregation:

SELECT
    p.id,
    p.name,
    GROUP_CONCAT(t.name) AS tag_names
FROM posts p
LEFT JOIN tags t
    ON JSON_SEARCH(p.tag_ids, 'one', t.id) IS NOT NULL
GROUP BY
    p.id,
    p.name;

Demo

edited Aug 16, 2019 at 5:20

answered Aug 16, 2019 at 5:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Cheung Over a year ago

Amazing! It's like total magic. I've never thought that joining and the JSON_SEARCH function can be used this way. I've learnt a lot. Thanks

Daniel Cheung Over a year ago

In reality, the table has several of these columns. I've tested them but MaiaDB refused to group multiple of them: dbfiddle.uk/… Anyway to fix this?

Tim Biegeleisen Over a year ago

@Danilel You should open a new question if you have a follow-up.

Collectives™ on Stack Overflow

MariaDB/MySQL select query replace json array of ids into concatenated values

posts

tags

1 Answer 1

Demo

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

posts

tags

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related