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:
What I want, is to group the Entities & Services so I can feed a Treeview component, like so:
Is this something I have to do on the API side, or can I "map" it in Angular?
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"
}
}
}
}
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"},
]
}
Is this something I have to do on the API side, or can I "map" it in Angular?
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:
Client side:
The Entity interface:
Basically, what I want is to transform the data, so it fits the Treeview component, like this:
thus giving me this result:
I hope I that was clear enought. Thank you so much for your help, man...
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:
Client side:
The Entity interface:
Basically, what I want is to transform the data, so it fits the Treeview component, like this:
thus giving me this result:
I 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
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]
Now in your Angular code you should define your service like this (note the Entity[] specified as an array)
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(
...
)
}
}
There is a glaring error in this code. Let's look at what is happeningLets 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);
[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();
}
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);
}
ASKER
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,
The 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):
These are the lines:
Thank you so much for your help, so far. I really appreciate it.
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);
}
The values passed in the $r parameter reach the function, but then the returning property values are all null as seen here:The 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):
These 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
]
]
];
}
What am I doing wrong?Thank you so much for your help, so far. I really appreciate it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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:
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...
But the returned value does not fit the Treeview component.
I have to group the entity and location properties like this:
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.
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.
ASKER
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, … }
3: Object { text: "EMGFA", value: null, collapsed: true, … }
4: Object { text: "EMGFA", value: null, collapsed: true, … }
5: Object { text: "EMGFA", value: null, collapsed: true, … }
...
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.
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.
ASKER
@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:
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...
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);
}
to read: // Convert to return structure
// Ensure we add to the array - not overwrite it
$data = getDataItem($rows);
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;
}
thus producing this response: Copied Response #2.txtI 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
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);
ASKER
@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:
I need it to be like this, because thats the format of the TreeViewItem parameter...
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"}]}]}]
this:{"text":"EMGFA","value":null, ..., {"text":"Urg\u00eancia","value":"PP112"}]}]}
Notice the absence of the starting and ending '[ ]'....I need it to be like this, because thats the format of the TreeViewItem parameter...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are most welcome Gustavo - glad you got sorted.
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
Open in new window
Open in new window