JSON to MYSQL with nested queries

Question

Looking for your experience in converting a complicated nested MYSQL query to a JSON file. Below is the code.

SELECT post_id
     , name
     , Email
     , CustomerId
     , DeliveryDate
     , DeliveryTime
     , DeliveryType
     , Zip
     , OrderNote
     , PaymentTotal
     , OrderStatus
  FROM ( SELECT t1.post_id
              , t2.name
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Email
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as CustomerId
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryDate
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryTime
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryType
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Zip
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderNote
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as PaymentTotal
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderStatus
           FROM table_A t1
         INNER 
           JOIN table_B t2 
             ON FIND_IN_SET(t1.post_id, t2.payment_ids)  
         GROUP 
             BY t1.post_id
              , t2.name  
       ) AS derived_table
 WHERE OrderStatus RLIKE '%trans%|ready'
   AND DeliveryDate >= CURRENT_DATE - INTERVAL 7 DAY
   AND DeliveryType = 'pickup'

Since this has to start with SELECT is there a way to convert this to JSON properly and what would it look like? As I understand it, JSON doesnt like when you start with the SELECT

I presume all the 'value' strings are actually different and represent the appropriate key for the value you are trying to extract? — Nick
– Nick, Commented Aug 19, 2020 at 2:24
corerct. 'value' is a placeholder so my answer isnt so specific. I am running 5.7.3 — Tim
– Tim, Commented Aug 19, 2020 at 3:01
Thanks I would love to but dont have enough Karma to vote. It says, thanks your response has been recorded but doesnt count — Tim
– Tim, Commented Aug 20, 2020 at 19:53

Nick · Accepted Answer · 2020-08-19 03:36:01Z

1

You can use JSON_OBJECT to create a JSON object out of each row that your query produces. These objects then need to be gathered into an array. Since you're not using MySQL 8+, you will need to simulate the JSON_ARRAYAGG by using GROUP_CONCAT instead. For example (I've left out parts of your query for simplicity):

SELECT CONCAT('[', GROUP_CONCAT(obj), ']') AS JSON
FROM (
    SELECT JSON_OBJECT('post_id', post_id
                     , 'name', name
                     , 'Email', Email
                     , 'CustomerId', CustomerId
                       -- ...
                      ) AS obj
    FROM (SELECT t1.post_id
               , t2.name
               , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Email
               , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as CustomerId
               -- ...
          FROM table_A t1
          INNER JOIN table_B t2 ON FIND_IN_SET(t1.post_id, t2.payment_ids)  
          GROUP BY t1.post_id, t2.name  
    ) AS derived_table
    WHERE OrderStatus RLIKE '%trans%|ready'
      AND DeliveryDate >= CURRENT_DATE - INTERVAL 7 DAY
      AND DeliveryType = 'pickup'
) j

Small demo on dbfiddle

edited Aug 19, 2020 at 3:36

answered Aug 19, 2020 at 3:26

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Tim Over a year ago

Thanks. Is there a major advantage here to moving to MYSQL 8?

Nick Over a year ago

@Tim there are many advantages, especially with the use of CTEs and window functions, although for this query it wouldn't make that much difference, you'd just change the first line to SELECT JSON_ARRAYAGG(obj)

Collectives™ on Stack Overflow

JSON to MYSQL with nested queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related