是否存在可以完全以这种方式编码为JSON的MySQL查询? [英] Is there a MySQL query that can encode to JSON in exactly this way?

问题描述

我有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屋！