UNION Tables MySQL and output into JSON Array

Question

I need some help on MySQL Query for UNION multiple Tables and group the result by ID and output the result in JSON_ARRAY. I have referred to this but I can't get the output as I want because my query need to UNION tables.

SELECT
    JSON_ARRAYAGG(JSON_OBJECT(
        '_CODE', a._CODE,
        '_USERID', a._USERID,
        '_NAME', c._NAME,
        'HAHA', 
        (SELECT JSON_ARRAY(JSON_OBJECT(
                '_CAT', b._CAT,
                '_MODEL', b._MODEL,
                '_SERIAL', b._SERIAL,
                '_IPADDR', b._IPADDR,
                '_OS', (SELECT _DESC 
                          FROM kod_produk 
                         WHERE _CODE = (SELECT _PROD 
                                          FROM inventori_dt3 
                                         WHERE _CODE = a._CODE 
                                           AND _CAT = '15' 
                                           AND _SERIAL = b._SERIAL)),
                 '_AV', (SELECT CONCAT(_DESC, 
                                       ' (', 
                                      (CASE WHEN(SELECT _CODE 
                                                   FROM inventori_dt3 
                                                  WHERE _CODE = a._CODE 
                                                   AND _CAT = '16' 
                                                   AND _EXPY_DATE > NOW()) 
                                                IS NOT NULL 
                                            THEN 
                                                 'AKTIF' 
                                            ELSE 
                                                 'TAMAT' 
                                             END), ')') 
                           FROM kod_produk 
                          WHERE _CODE = (SELECT _PROD 
                                           FROM inventori_dt3 
                                          WHERE _CODE = a._CODE 
                                            AND _CAT = '16' 
                                            AND _SERIAL = b._SERIAL))
                 )))
        )) Z
FROM
    inventori_mt a
LEFT JOIN
    (
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt1
        UNION ALL
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt2
    ) b ON a._CODE = b._CODE
LEFT JOIN
    sys_pengguna c ON a._USERID = c._USERID

Table design

Pastebin
Expected Result
Actual Output Result

DB-Fiddle
Sample DB

@ThomasG I have put the Expected Result and the Actual Result here but StackOverflow doesnt allow me to put too many code. I have paste those code on pastebin. Sorry — ArnabBuluss, Sep 08 '20 at 07:16
Hi sorry Im forgot to add the `kod_produk` to the table structure.. I have updated the complete structure for this query.. @BarbarosÖzhan — ArnabBuluss, Sep 09 '20 at 02:07

Barbaros Özhan · Accepted Answer · 2020-09-10T13:46:02.537

Need to use JSON_ARRAY() with JSON_ARRAYAGG() within the subquery, and use GROUP BY expression within the one level outer query with JSON_OBJECT() function at topmost of this query in order to get each individual grouped, and nested objects. And apply JSON_ARRAYAGG() at the outermost level in order to aggregate all those independent JSON object pieces. So, use :

SELECT JSON_PRETTY( JSON_ARRAYAGG(Z) ) AS Z
  FROM
  (
  SELECT 
    JSON_OBJECT(
                '_CODE', a._CODE,
                '_USERID', a._USERID,
                '_NAME', c._NAME,
                'HAHA', 
                (SELECT 
                JSON_ARRAYAGG(
                JSON_OBJECT(
                '_CAT', b._CAT,
                '_MODEL', b._MODEL,
                '_SERIAL', b._SERIAL,
                '_IPADDR', b._IPADDR ,
                '_OS', (SELECT _DESC 
                          FROM kod_produk 
                         WHERE _CODE = (SELECT _PROD 
                                          FROM inventori_dt3 
                                         WHERE _CODE = a._CODE 
                                           AND _CAT = '15' 
                                           AND _SERIAL = b._SERIAL)),
                 '_AV', (SELECT CONCAT(_DESC, 
                                       ' (', 
                                      (CASE WHEN(SELECT _CODE 
                                                   FROM inventori_dt3 
                                                  WHERE _CODE = a._CODE 
                                                   AND _CAT = '16' 
                                                   AND _EXPY_DATE > NOW()) 
                                                IS NOT NULL 
                                            THEN 
                                                 'AKTIF' 
                                            ELSE 
                                                 'TAMAT' 
                                             END), ')') 
                           FROM kod_produk 
                          WHERE _CODE = (SELECT _PROD 
                                           FROM inventori_dt3 
                                          WHERE _CODE = a._CODE 
                                            AND _CAT = '16' 
                                            AND _SERIAL = b._SERIAL)) 
                                         )
                               )
                           )
                 ) Z
  FROM inventori_mt a
  LEFT JOIN
       (
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt1
        UNION ALL
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt2 
       ) b 
         ON a._CODE = b._CODE
  LEFT JOIN sys_pengguna c 
         ON a._USERID = c._USERID
 GROUP BY a._CODE, a._USERID, c._NAME   
 ) q

Demo

Thanks for your time helping me. I have tried put those **LEFT JOIN** within returning values but it doesn't work.. — ArnabBuluss, Sep 08 '20 at 08:22
you're welcome @ArnabBuluss . We have no sample data, but I think the issue is related with missing outer join. Let it try with **RIGHT JOIN** which should resolve... Since. noticed that the **null** value comes from `kod_produk` which's stated left hand side. — Barbaros Özhan, Sep 08 '20 at 08:42
I have tried change if to `RIGHT JOIN` but got the subquery return more than 1 row error. — ArnabBuluss, Sep 09 '20 at 02:33
we need to see the sample data set in order to be able to interpret @ArnabBuluss — Barbaros Özhan, Sep 09 '20 at 11:25
Hi @BarbarosÖzhan.. I have edit the post with the link to my sample database.. Thanks — ArnabBuluss, Sep 10 '20 at 03:24
Thank you so much @BarbarosÖzhan for your time on helping me.. It works perfectly.. — ArnabBuluss, Sep 27 '20 at 02:00

UNION Tables MySQL and output into JSON Array

1 Answers1