是否存在可以完全以这种方式编码为JSON的MySQL查询? [英] Is there a MySQL query that can encode to JSON in exactly this way?
本文介绍了是否存在可以完全以这种方式编码为JSON的MySQL查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2张桌子
work_assets
-----------------------------------------------------
id | asseturl | previmgurl | carid
-----------------------------------------------------
1 | "pic1.jpg" | "pic2.jpg" | 1
2 | "pic3.jpg" | "pic4.jpg" | 1
3 | "pic5.jpg" | "pic6.jpg" | 2
. | ... | ... | .
. | ... | ... | .
cases
-----------------------------------------------------
id | ... | carid | ...
-----------------------------------------------------
1 | ... | 1 | ...
2 | ... | 2 | ...
3 | ... | 69 | ...
. | ... | ... | ...
. | ... | ... | ...
,我希望我可以执行某种类型的查询Q
,该查询可以将我带到JSON,而该JSON实际上是cases
表,从其carid
列到关联的asseturl
和previmgurl
,例如
and my hope is that I can perform some type of query Q
that can get me to JSON that is basically the cases
table with a one-to-many relationship from its carid
colummn to the associated asseturl
and previmgurl
, like
[
{ id : 1, ..., assetinfo: [ { asseturl : "pic1.jpg", previmgurl: "pic2.jpg"}, { asseturl : "pic3.jpg", previmgurl: "pic4.jpg"} ], ... },
{ id : 2, ... },
{ id: 3, ... },
.
.
.
]
设置:
var cases = <?php
$Q = ????;
echo json_encode($wpdb->get_results($Q));
?>
这可能吗?
推荐答案
恕我直言,您真的不需要在mysql方面进行json转换,因为它效率不高而且几乎没有意义.
IMHO you really don't need to do that json transformation on mysql side because it is not very efficient and has almost no sense.
但是只是为了证明这是可能的(与@bannmatt意见相反),这是我的方法:
But just to show that it is possible (in opposite of @bannmatt opinion) here is my approach:
http://sqlfiddle.com/#!9/6bffb/7
SELECT c.*,
CONCAT('assetinfo : [ ',
COALESCE(
GROUP_CONCAT( CONCAT("{ asseturl: ",
wa.asseturl,
', previmgurl: ',
wa.previmgurl," }")
),
''),
' ]')
FROM cases c
LEFT JOIN work_assets wa
ON c.carid = wa.carid
GROUP BY c.id
这篇关于是否存在可以完全以这种方式编码为JSON的MySQL查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文