Flat MySQL query to nested JSON objects

Question

Is there a quick way without using a complex series of for loops (using something like Underscore? Or an improvement on my MySQL query?) to take my data that comes formatted in a flat list like this:

[
  {
    "J_NUM": "BOAK-1212",
    "X_DUE_DATE": "2012-06-20T00:00:00.000Z",
    "X_LEAD_TIME": 0,
    "X_NAME": "Mail List Due",
  },
  {
    "J_NUM": "BOAK-1212",
    "X_DUE_DATE": "2012-06-08T00:00:00.000Z",
    "X_LEAD_TIME": 0,
    "X_NAME": "Vendor Specs 2",
  },
  {
    "J_NUM": "JEFF-2000",
    "X_DUE_DATE": "2012-06-11T00:00:00.000Z",
    "X_LEAD_TIME": 0,
    "X_NAME": "Client Final Approval",
  },
  {
    "J_NUM": "JEFF-2000",
    "X_DUE_DATE": "2012-06-08T00:00:00.000Z",
    "X_LEAD_TIME": 0,
    "X_NAME": "Vendor Specs 2",
  }
]

And nest/group it based on certain parameters, in this case J_NUM, so that it comes out like this:

[
  {
    "J_NUM": "BOAK-1212",
    "SCHEDULE_SERIES": [
      {
        "X_DUE_DATE": "2012-06-20T00:00:00.000Z",
        "X_LEAD_TIME": 0,
        "X_NAME": "Mail List Due",
      },
      {
        "X_DUE_DATE": "2012-06-08T00:00:00.000Z",
        "X_LEAD_TIME": 0,
        "X_NAME": "Vendor Specs 2",
      }
    ]
  },
  {
    "J_NUM": "JEFF-2000",
    "SCHEDULE_SERIES": [
      {
        "X_DUE_DATE": "2012-06-11T00:00:00.000Z",
        "X_LEAD_TIME": 0,
        "X_NAME": "Client Final Approval",
      },
      {
        "X_DUE_DATE": "2012-06-08T00:00:00.000Z",
        "X_LEAD_TIME": 0,
        "X_NAME": "Vendor Specs 2",
      }
    ]
  }
]

Majid Fouladpour · Accepted Answer · 2013-10-11 22:30:38Z

2

In your while loop you can build the output you want:

$query  = "SELECT ...";
$result = mysqli_query($link, $query);
$out = array();
while($row=mysqli_fetch_assoc($result)) {
  $J_NUM = $row['J_NUM'];
  if(!array_key_exists($J_NUM, $out)) {
    $out[$J_NUM] = array("J_NUM" =>  $J_NUM, "SCHEDULE_SERIES" => array());
  }
  $out[$J_NUM]['SCHEDULE_SERIES'][] = array(
    "X_DUE_DATE"  => $row['X_DUE_DATE'],
    "X_LEAD_TIME" => $row['X_LEAD_TIME'],
    "X_NAME"      => $row['X_NAME']); 
}
$out = json_encode(array_values($out), true);

edited Oct 11, 2013 at 22:30

answered Oct 11, 2013 at 22:24

Majid Fouladpour

30.4k21 gold badges80 silver badges129 bronze badges

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

1 Comment

371273 Over a year ago

Thank you so much for your help, but I was looking for something in JavaScript. I found the solution using groupBy from the Underscore library.

371273 · Accepted Answer · 2013-10-14 02:10:20Z

0

Figured it out using Underscore.

_.groupBy(theData, 'J_NUM') will return the following:

{"BOAK-1212":[
    {"J_NUM":"BOAK-1212",
    "X_DUE_DATE":"2012-06-20T00:00:00.000Z",
    "X_LEAD_TIME":0,
    "X_NAME":"Mail List Due"},
    {"J_NUM":"BOAK-1212",
    "X_DUE_DATE":"2012-06-08T00:00:00.000Z",
    "X_LEAD_TIME":0,
    "X_NAME":"Vendor Specs 2"}
],
"JEFF-2000":[
    {"J_NUM":"JEFF-2000",
    "X_DUE_DATE":"2012-06-11T00:00:00.000Z",
    "X_LEAD_TIME":0,
    "X_NAME":"Client Final Approval"},
    {"J_NUM":"JEFF-2000",
    "X_DUE_DATE":"2012-06-08T00:00:00.000Z",
    "X_LEAD_TIME":0,
    "X_NAME":"Vendor Specs 2"}
]}

answered Oct 14, 2013 at 2:10

371273

5,46811 gold badges51 silver badges69 bronze badges

Collectives™ on Stack Overflow

Flat MySQL query to nested JSON objects

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related