Link to home
Start Free TrialLog in
Avatar of Gustavo Baptista
Gustavo Baptista

asked on

How do I convert JSON response to structured object?

Hi Experts,
this is the response I'm getting from my API:
{"0": 
   {
     "text": "Entity_1",
     "value": "",
     "collapsed": true,
     "children": {
        "text": "Location_1",
        "value": "",
        "children": {
           "text": "Service_1",
           "value": "370"
        }
     }
   }
}
{
    "1": {
        "text": "Entity_1",
        "value": "",
        "collapsed": true,
        "children": {
            "text": "Location_1",
            "value": "",
            "children": {
                "text": "Service_2",
                "value": "61"
            }
        }
    }
}
{
    "95": {
        "text": "Entity_2",
        "value": "",
        "collapsed": true,
        "children": {
            "text": "Location_2",
            "value": "",
            "children": {
                "text": "Service_45",
                "value": "PP124"
            }
        }
    }
}

Open in new window


What I want, is to group the Entities & Services so I can feed a Treeview component, like so:
{
   text: "Entity_1",
   value: "",
   collapsed: true,
   children: {
      text: "Location_1",
      value: "",
      children: [
         { text: "Service_1", value: "370"},
         { text: "Service_2", value: "61"}
      ]
}
{
   text: "Entity_2",
   collapsed: true,
   children: {
      text: "Location_1",
      value: "",
      children: [
         { text: "Service_45", value: "PP124"},
      ]
}

Open in new window


Is this something I have to do on the API side, or can I "map" it in Angular?
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Angular will automatically convert it to an object for you.
However, your JSON is not valid. You have multiple objects one after the other without a separator. Either these need to be sent individually
OR
You need to make them an array i.e. wrap in [ ] and put ',' between.
Having said that your data is confusing as you have objects with what appears to be array indexes INSIDE the object - so your first step is to figure out what you are meant to be sending back and get the data sorted out.

When you are done with that you can query the data like this

For example in your service
import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';

// Other imports here if necessary

export interface MyData {
  text: string;
  value: string;
  collapsed: boolean,
  children: {
    text: string;
    value: string;
    children: {
      text: string;
      value: string;
    }
  }
}

Injectable({
  providedIn: 'root'
})
export class MyService {
  construct(private http: HttpClient) {}

  /*
   * ...
   * Other service items here
   * ...
   */
  getData() : Observable<any> {
    return this.http.get<any>('yoururl')
  }

  /*
   * ...
   * Other service items here
   * ...
   */
}

Open in new window

In your component
...
construct(private svc: MyService) {}


ngOnInit() {
  // For illustration only. Usually this would be linked to an async pipe in your view
  // or you would have some mechanism to manage the clean up of the subscription
  this.svc.getData().subscribe(data => console.log(data);
}

Open in new window

Avatar of Gustavo Baptista
Gustavo Baptista

ASKER

Cheers Julian Hansen,

Thanks for the input.
So here is the whole 'story':

I have a series of values stored in a table, whitch I fetch using this code:
Server side:
User generated image
Client side:
User generated imageThe Entity interface:
User generated image
Basically, what I want is to transform the data, so it fits the Treeview component, like this:
User generated imagethus giving me this result:
User generated imageI hope I that was clear enought. Thank you so much for your help, man...

@Gustavo,
In future - don't post screen grabs - if we need to copy your code to show corrections we can't do it with an image. Cut and paste your code into the post, highlight it and click the code button in the toolbar - this will format it nicely in the post.

Let's start with the first problem - your PHP code
$data = array();
$sql = "SELECT * FROM `services` ORDER BY `entity`, `location`";
$response = mysqli_query($conn, $sql);

if (mysqli_num_rows($response) > 0) {
  while($result = mysqli_fetch_object($response)) {
    $data = array(
      ...
    )
  }
}

Open in new window

There is a glaring error in this code. Let's look at what is happening

Lets say your query returns 3 rows (A, B, C)
Enter the while loop - first iteration
$data = array( with values from A)
Enter the second iteration
$data = array (with values from B) // What happened to the setting from the previous iteration?

Your loop is destructive - it is overwriting the $data variable on each iteration.

Let's look at how you can fix this. (I am going to use PDO here because it makes your life so much easier when dealing with databases - the key bits will remain compatible with your code if you choose to stay with mysqli.)
PDO Setup (somewhere else in the script).

[database.php]
<?php
define('DB_NAME','database_name');
define('DB_SERVER,'localhost');
define('DB_USER,'username');
define('DB_PASS,'password');

$dsn = 'mysql:dbname=' . DB_NAME . ';host=' . DB_SERVER;
$db = new PDO($dsn, DB_USER, DB_PASS);

Open in new window

[getServices.php]
<?php
require_once('lib/database.php');

// Our default return
$data = [];

$sql = "SELECT * FROM `services` ORDER BY `entity`, `location`";
$stmt = $db->query($sql);
if ($stmt) {
   // PDO Function that fetches all results in one call
   $rows = $stmt->fetchAll();

   // Convert to return structure
   foreach($rows as $r) {

     // Ensure we add to the array - not overwrite it
     $data[] = getDataItem($r);
   }
}

// Return the data using a utility function that
// sets the header and terminates the script.
sendJSON($data);

/*
 * Utility to convert databaes row to TreeView object.
 */
function getDataItem($item) {
  return [
    'text' => $item->entity,
    'value' => null,
    'children' => [
      'text' => $item->location,
      'value' => null,
        'children' => [
          'text' => $item->service_description,
          'value' => $item->service_code,
       ]
    ]
  ];
}

/*
 * Utility to send JSON back to the client.
 * Usually located in a helper script
 */
function sendJSON($resp, $terminate = true) {
  header('Content-type: application/json');
  echo json_encode($resp);
  if ($terminate) die();
}

Open in new window


Now in your Angular code you should define your service like this (note the Entity[] specified as an array)
getServices(): Observable<Entity[]> {
  const url = this.baseUrl + 'getServices.php';
  return this.http.get<Entity[]>(url);
}

Open in new window


Cheers @Julian Hansen,
Thanks for the heads up on the 'screen grabs'. I'm kind of a noob posting here, but what you said makes perfect sense. I'll correct it going forward.

I've tried your code, but something is not going well.
When the code reaches these lines,
// Convert to return structure
  foreach ($rows as $r) {
    // Ensure we add to the array - not overwrite it
    $data[] = getDataItem($r);
  }

Open in new window

The values passed in the $r parameter reach the function, but then the returning property values are all null as seen here:
User generated imageThe foreach loop re-runs after 5 iterations, and resets (as expected) the $data array, thus creating an infinite loop.

I've turn on PHP error logging and this is what I get in the error_log file (I've shortened the path for 'privacy' issues):
User generated imageThese are the lines:
function getDataItem($item)
{
  return [
    'text' => $item->entity, <-- line 43
    'value' => null,
    'children' => [
      'text' => $item->location, <-- line 46
      'value' => null,
        'children' => [
          'text' => $item->service_description, <-- line 49
          'value' => $item->service_code, <-- line 50
        ]
    ]
  ];
}

Open in new window

What am I doing wrong?
Thank you so much for your help, so far. I really appreciate it.
SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Julian Hansen, thanks a bunch man.

But the returned value does not fit the Treeview component.
I have to group the entity and location properties like this:
User generated image
I leave you a link to the ngx-treeview component so that you better understand what I mean...
https://www.npmjs.com/package/ngx-treeview

Once again, thank you so much for your precious help...
Ok, we are moving into a different space. Can we establish whether or not we have solved the actual problem this question was addressing in other words - is the data coming through.

If we have solved that part - we can move on to the next which is how to format the data correctly for the tree control. This would need to happen in your PHP script.

The code I gave you should give you records in the format you were trying to do before - if that is not the correct format we need to look at what the format should be and fix your PHP script.

Can you capture the Response from the POST in the console and paste it here so we can see what is coming back.

Copy the JSON return and paste it here.
Ok. I already signaled the previous answer as valid. Tkx so much again.
Now for the JSON response, here it is:

Array(111) [ {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, … ]
[0…99]
0: Object { text: "EMGFA", value: null, collapsed: true, … }
children: Object { text: "HFAR-PL", value: null, children: {…} }
collapsed: true
text: "EMGFA"
value: null
<prototype>: Object { … }
1: Object { text: "EMGFA", value: null, collapsed: true, … }children: Object { text: "HFAR-PL", value: null, children: {…} }
collapsed: true
text: "EMGFA"
value: null
<prototype>: Object { … }
2: Object { text: "EMGFA", value: null, collapsed: true, … }
3: Object { text: "EMGFA", value: null, collapsed: true, … }
4: Object { text: "EMGFA", value: null, collapsed: true, … }
5: Object { text: "EMGFA", value: null, collapsed: true, … }
...
@Gustavo

The response you posted above is not the JSON response - this is the console output which contains part of the response.

What you need to do is copy the actual text response.
1. F12
2. Go to the Network tab
3. Initiate the API request
4. Right click the request in the network console and select Copy Response
5. Paste that here.


@Julian, sorry, my bad...
Here is the response: Copied Response.txt

Nevertheless, I tried on my end, to come up with an answer and I think I'm almost there.
I changed this row:
// Convert to return structure
  foreach ($rows as $r) {
    // Ensure we add to the array - not overwrite it
    $data[] = getDataItem($r);
  }

Open in new window

to read:
  // Convert to return structure
  // Ensure we add to the array - not overwrite it
  $data = getDataItem($rows);

Open in new window

and changed the getDataItem() to read:
 * Utility to convert databases row to TreeView object.
 */
function getDataItem($item)
{
  $curEnt = $item[0]->entity; //Get current Entity value for later comparison
  $curLoc = $item[0]->location; //Get current Location value for later comparison

  foreach ($item as $index) {
    (object)$services[] = [
      'text' => $index->service_description,
      'value' => $index->service_code
    ];

    if ($index->location !== $curLoc) {
      (object)$location[] = [
        'text' => $curLoc,
        'value' => null,
        'collapsed' => true,
        'children' => $services
      ];
      $curLoc = $index->location;
      (object)$services = [];
    }

    if ($index->entity !== $curEnt) {
      (object)$entity[] = [
        'text' => $curEnt,
        'value' => null,
        'collapsed' => true,
        'children' => $location
      ];

      $curEnt = $index->entity;
      $services = (object)[];
      $location = (object)[];
    } elseif ($index->id === (string)count($item)) {
      (object)$location[] = [
        'text' => $curLoc,
        'value' => null,
        'collapsed' => true,
        'children' => $services
      ];

      (object)$entity[] = [
        'text' => $curEnt,
        'value' => null,
        'collapsed' => true,
        'children' => $location
      ];
    }
  }

  return $entity;
}

Open in new window

thus producing this response: Copied Response #2.txt
I think we are almost there. I compared this response to the Treeview component object, and the only difference is the beginning and ending "[ ]". These transform the response in an array, and the Treeview component takes a pure object as parameter. I have tried to eliminate these "[ ]" unsuccessfully.

Am I thinking correctly? And do you think that the getDataItem() code could be cleaner?

Tkx man. Cheers...
Ok I see what you need.
You can try using this process
// What we want in the end
$tree = [];
// Keep track of nodes we have already seen
$index = [];

foreach($rows as $row) {
  // Check if we have seen this top level item
  $key_1 = $row->entity;
  if (empty($index[$key_1])) {
  
    // No? Create it and add it to the tree
    $item = (object) [
      'text' => $row->entity,
      'value' => null,
      'children' => []
    ];
    $index[$key_1] = $item;
    $tree[] = $item;
  }

  // Check if we have this second level item in the index
  $key_2 = $key_1 . ':' . $row->location;
  if (empty($index[$key_2])) {

    // No? Create it, add it to the index and also add it
    // to the current top level item
    $item = (object) [
      'text' => $row->location,
      'value' => null,
      'children' => []
    ];
    $index[$key_2] = $item;
    $index[$key_1]->children[] = $item;
  }

  // Same process for level 3 except now we set the value
  $key_3 = $key_2 . ':' . $row->description;
  if (empty($index[$key_3])) {
    $item = (object) [
      'text' => $row->description,
      'value' => $row->service_code
    ];
    $index[$key_3] = $item;
    $index[$key_2]->children[] = $item;
  }
}

// Now send the data back as JSON
sendJSON($tree);

Open in new window

@Julian, its almost there...
The code is a lot cleaner, and faster, but is it possible to send the JSON without the starting and ending '[ ]'?
Instead of this:
[{"text":"EMGFA","value":null, ..., {"text":"Urg\u00eancia","value":"PP112"}]}]}]

Open in new window

this:
{"text":"EMGFA","value":null, ..., {"text":"Urg\u00eancia","value":"PP112"}]}]}

Open in new window

Notice the absence of the starting and ending '[ ]'....
I need it to be like this, because thats the format of the TreeViewItem parameter...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Julian,

I can't thank you enough for your precious time and dedicated help.
It's working like a charm. Just need to make some tunning....
User generated image
You are most welcome Gustavo - glad you got sorted.