MariaDB/MySQL选择查询将ID的json数组替换为串联值 [英] MariaDB/MySQL select query replace json array of ids into concatenated values

问题描述

我对数据库查询不太熟悉，但是我想执行以下操作:在一个表中，我存储了一列标记ID，例如:[1,6,8]，并且希望返回"Music, Dance, Pop"，例如我的选择查询.

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.

我没有设计数据库架构，也不允许修改它.

我要这样做的原因是，此查询的响应将直接发送到客户端以进行表呈现，并且使用当前的ORM检索(PHP Laravel)，这需要很长时间来检索相同的相关模型每行. 所以我想知道是否可以进行这样的查询.

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.

我的模式如下:

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

标签

id | name

我曾经考虑过使用JSON_REPLACE，但是根本不知道如何解决此问题.甚至在没有代码后处理结果的普通SQL中，这是否有可能?

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?

推荐答案

并且我不允许对其进行修改

and I'm not allowed to modify it

理想情况下，您可以考虑对数据进行规范化，这将使处理此问题更加容易.话虽这么说，借助于JSON函数，我们可以尝试结合聚合的联接:

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;

演示

这篇关于MariaDB/MySQL选择查询将ID的json数组替换为串联值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！