Transforming a simple MySQL query into a structured nested JSON format

Is there a way to easily reorganize data without using complex for loops (perhaps with Underscore.js or refining the MySQL query)? I have data formatted 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",
  }
]

What is the best method to group/nest this data based on a specific parameter, such as J_NUM? The desired output would be structured 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",
      }
    ]
  }
]
javascriptmysqljsonobject

Answer №1

Inside your while loop, you have the opportunity to construct the desired output: 

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

Answer №2

After some careful analysis with Underscore, I managed to crack the code.

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

{"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"}
]}

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Is the Order of a JSON Array Dependable?

When working with JSON, it's clear that relying on the ordering of key-value pairs may not be reliable. For instance, a JSON parser could interpret { "someKey" : "someValue", "anotherKey" : "anotherValue", "evenAnotherKey" : "evenAnotherV ...

javascriptjson

Using SVG files in NextJS

I'm encountering an issue while trying to import an SVG file in a NextJS project. The error message I keep getting is: ./assets/aboutimg.svg 1:0 Module parse failed: Unexpected token (1:0) You may need an appropriate loader to handle this file type, ...

javascriptreactjsnext.js

JSONPath: Finding data based on dates

Is it possible to use JSON path to filter data by date range if you have JSON data with dates as keys for the objects? For instance, in the given example, how can you retrieve all book titles with publication dates falling between specific x and y dates? ...

json

Retrieving both keys and values from a JSON String with BasicDBObject when working with a list

My String holds a JSON data with a list structure as shown below: { "counts":[ {"foo827138": 123124, "bar2918":312312, "something_else321-313":2321321}, {"foo1231412": 4321, "bar1231515":123, "something_else3012931":7282}, {"fo ...

javajsonmongodblist

Choose the option from the jQuery dropdown list based on the displayed text instead of the value

In continuation of my previous question on jQuery getting values from multiple selects together, I am working with a select list like this: <select name="access" class="change" id="staff_off" runat="server"> <option value="8192">Off< ...

javascriptjqueryasp.net

Identifying functions that contain dots in their names using JSHint

While running jshint on a JavaScript file, I encountered functions with dots in their names for namespacing purposes. Specifically, within the d3 library, there is a significant portion of code that resembles: d3.select("something") Should I simply disab ...

javascriptd3.jsjshintgruntjs

How can I alter the icon's color?

Is it possible for the icon's color to change to red when the condition is greater than 0, and to gray when the condition is equal to zero? <TouchableOpacity onPress={() => { if (Object.values(selectedIt ...

javascriptreactjsreact-native

I attempted to create a callable function for creating a user, but I encountered an error when running it and I am unable to determine the cause

I'm in the process of creating a user management page and have set up a callable function on Firebase to handle the creation of new users. The HTML function I've designed is supposed to update existing users or create new ones. However, when test ...

javascriptfirebasefirebase-authenticationgoogle-cloud-functions

What exactly is the mechanism behind the functionality of ng-cloak?

Recently, I've been delving into the ng-cloak source code and trying to understand its inner workings. If you're interested, you can take a look at the source code here. From what I gather, it seems that the ng-cloak attribute is removed during ...

javascriptangularjs

Is it possible for the original object to be altered when passing it as a parameter to a function in a different file?

When you update an object passed as a parameter, will the updates be reflected "upwards" if the method receiving the parameter is in a different file? Or will the object retain its own context despite being passed down? ...

javascript

Traverse an array in JavaScript and display the elements

I am new to JavaScript and struggling with a question. I have an array of 120 numbers that I want to loop through, printing out specific words based on certain conditions. For example, if a number is divisible by 3, I need to print "Go", if divisible by 5, ...

javascriptjqueryarraysif-statementfor-loop

Obtaining only string values when looping through JSON information

JSON: { "status": "success", "data": { "9": { "1695056": { "id": "1695056", [...] }, "csevents": { "2807": { "id": "280 ...

pythonjsonapi

Position a div element after another using the :after pseudo-element

My goal is simple to explain, but I have exhausted all my efforts trying to achieve it. I am hoping for the ★ symbol to appear immediately after the number 06 using jQuery. Any assistance would be greatly appreciated. The numbers are generated by a s ...

javascriptjqueryhtmlcss

Transforming nested JSON files into nested jQuery divs

Is it possible to iterate through two JSON files that have a parent-child relationship based on simple ID primary and foreign keys? Can we then display the data in a list of divs with the following characteristics: Hierarchical - child divs should only a ...

javascriptjqueryhtmlcssjson

Utilize Javascript to acquire the redirected URL

Seeking assistance with making a GET call to an API endpoint that redirects me. I have enabled CORS to access the Location, but struggling to find a way through Ajax or Fetch to retrieve only the Redirect URL. Any help would be highly appreciated. ...

javascriptajaxget

Controlling the file system on a local level through browser-based JavaScript and Node.js

I am currently working on a project that requires users to interact with the file system directly from their browsers. While I have extensive experience in writing client-side JavaScript code and using Node.js scripts for tasks like web scraping, data anal ...

javascriptnode.js

Is there a way to extract an icon from an object or array?

Currently, I am facing challenges in extracting an icon from either an object or an array for a project I am working on. My approach involves storing the icon in a variable and passing it as a prop to another component. However, the functionality is not wo ...

javascriptreactjsopenweathermap

NodeJS File Upload: A Step-by-Step Guide

I need assistance with uploading an image using nodejs. I am able to successfully send the file to node, but I am unsure how to handle the "req" object. Client <html> <body> <input id="uploadInput" type="file"/> < ...

javascripthtmlajaxnode.js

Build a nested block containing a div, link, and image using jQuery or vanilla JavaScript

I have a button that, when clicked, generates a panel with 4 divs, multiple href links, and multiple images. I am new to web programming and understand that this functionality needs to be in the Javascript section, especially since it involves using jsPlum ...

javascriptjqueryhtmljsplumb

Having trouble decoding invalid JSON received from the Twilio API

For the past 4 hours, I've been struggling to parse a basic JSON from Twilio. The process is as follows: A text message containing a magnet link is sent Twilio forwards the request to my serverless function in the cloud I attempt to parse the reques ...

javascriptnode.jsjsonexpresstwilio