Extract key value pair output from JSON file using PHP

Question

I would like to extract key value pair output from JSON formatted file using PHP and put into html table & database with exact key column. I tried code mentioned in Extract JSON ouput to get line by line key pair values using PHP but it doesn't work for multiple lines and gives wrong output from 2nd line onwards itself due to multiple lines from 2nd key.

As we discussed in that, filing separate question to avoid clutter the same question.

JSON file content:

{"key":"SEM-5765","status":"Closed","components":"UX","affectedVersions":"SEM 8.8.x","fixVersions":"SurmaZuse-8.8.10","customerFacingInfo":"[https://goog.ezy.com/show_bug.cgi?id=109021 Bug 109021] - Content spoofing (text) via loginErrorCode \[CWE-345\]"} {"key":"SEM-3325","status":"Closed","components":"UX","affectedVersions":"SEM Prior to 8.7","fixVersions":"SurmaZuse-8.8.10","customerFacingInfo":"Fixed a number of bugs related to Delegated Admin in the Admin Console:
* \"New administrator\" creation button was not disabled for delegated admin without required rights ([https://goog.ezy.com/show_bug.cgi?id=108503 Bug 108503])
* \"Account Limits\" in domain settings could not be shown even when adminConsoleDomainLimitsTabRights was added ([https://goog.ezy.com/show_bug.cgi?id=108327 Bug 108327])
* Had been unable to remove \"Dynamic Group\" from distribution properties page ([https://goog.ezy.com/show_bug.cgi?id=108499 Bug 108499])
* After performing a bulk migration, the Delegated Admin user encountered an `HTTP Error 403` when attempting to download the list of provisioned accounts ([https://goog.ezy.com/show_bug.cgi?id=108539 Bug 108539])"} {"key":"SEM-2755","status":"Closed","components":"UX","affectedVersions":"SEM Prior to 8.7","fixVersions":"SurmaZuse-8.8.10","customerFacingInfo":"Global Admin can now control the Downloads View (Admin > Tools > Download) and Help Center view for Delegated Admins."}

Expected:

SEM-5765
Closed
UX
SEM 8.8.x
SurmaZuse-8.8.10
[https://goog.ezy.com/show_bug.cgi?id=109021 Bug 109021] - Content spoofing (text) via loginErrorCode \[CWE-345\]


SEM-3325
Closed
UX
SEM Prior to 8.7
SurmaZuse-8.8.10
Fixed a number of bugs related to Delegated Admin in the Admin Console: * \"New administrator\" creation button was not disabled for delegated admin without required rights ([https://goog.ezy.com/show_bug.cgi?id=108503 Bug 108503]) * \"Account Limits\" in domain settings could not be shown even when adminConsoleDomainLimitsTabRights was added ([https://goog.ezy.com/show_bug.cgi?id=108327 Bug 108327]) * Had been unable to remove \"Dynamic Group\" from distribution properties page ([https://goog.ezy.com/show_bug.cgi?id=108499 Bug 108499]) * After performing a bulk migration, the Delegated Admin user encountered an `HTTP Error 403` when attempting to download the list of provisioned accounts ([https://goog.ezy.com/show_bug.cgi?id=108539 Bug 108539])

SEM-2755
Closed
UX
SEM Prior to 8.7
SurmaZuse-8.8.10
Global Admin can now control the Downloads View (Admin > Tools > Download) and Help Center view for Delegated Admins.

Tried code:

echo "<table class='table create-release-note-table'>
                <thead>
                    <tr><th>#</th><th>Ticket ID</th><th>Status</th><th>Components</th><th>Affected Versions</th><th>Fix Versions</th><th>Description</th></tr>
                </thead>
            <tbody>";
    $i = 0;

    $resultFile = fopen($resultURL, "r");
    #$lines = file($resultURL, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    #print_r ($lines);
    #exit;

    while (!feof($resultFile)) {
        $line = trim(fgets ($resultFile));
        $line = str_replace("\\\"", "", $line);
        $line = stripslashes($line);
        $lineArray = json_decode($line, true);
        echo "<tr><td>" . ++$i . "</td>";
        parseData($lineArray);
        echo "</tr>";
    }
    echo "</tbody></table>";
    fclose ($resultFile);


// Parse release note data

function parseData($array) {
   $value = str_replace(",", ";", $value);
   foreach ($array as $key => $value) {
     if (is_bool($value)) {
        echo ("<td>" . $value? 'true' : '') . "</td>";
     } else {
        echo "<td>" . $value . "</td>";
     }
  }
}

Tommy89 · Accepted Answer · 2019-02-05T11:34:06.207

Your JSON seems not be formatted well in first place. You missed commas and square brackets.

This is a very basic solution but you can correct your JSON following this method:

Add commas

$json = str_replace("} {", "}, {", $original_json);

Clean a bit the code (this is rough. Good for your case but not the best at all!)

$json = str_replace("\[", "[", $json);
$json = str_replace("\]", "]", $json);

Wrap it inside [ ]

$your_json_string = "[" . $json . "]";

and then you can just use

$json_parsed = json_decode($your_json_string);

and

echo "<table class='table create-release-note-table'>
<thead>
<tr>
<th>#</th>
<th>Ticket ID</th>
<th>Status</th>
<th>Components</th>
<th>Affected Versions</th>
<th>Fix Versions</th>
<th>Description</th>
</tr>
</thead>
<tbody>";

foreach($json_parsed as $json_object){
   echo "<tr>";
   echo "<td></td>";
   echo "<td>" . $json_object->key . "</td>";
   echo "<td>" . $json_object->status. "</td>";
   echo "<td>" . $json_object->components. "</td>";
   echo "<td>" . $json_object->affectedVersions. "</td>";
   echo "<td>" . $json_object->fixVersions . "</td>";
   echo "<td>" . $json_object->customerFacingInfo . "</td>";
   echo "</tr>";
}
echo "</tbody>
</table>";

That's it

Can you please share exact code for the problem? FYI.. I'm aware that my JSON is partially formatted but unfortunately API gives me this response. — Jitesh Sojitra, Feb 05 '19 at 11:05
FYI...it didn't require to replace {, } and add [ ]. It worked fine by parsing lines starts with {key. — Jitesh Sojitra, Feb 06 '19 at 03:44

score 2 · Answer 2 · answered Feb 05 '19 at 10:58

2

I'm afraid the issue is on the json file side.

If I understand correctly, the JSON file content exposed in your question is supposed to be a single json file.

If so, the format of the json seems incorrect.

Your json is structured as follow (I remove some parts of the content to help clarify my point):

{"key":"SEM-5765"} 
{"key":"SEM-3325"} 
{"key":"SEM-2755"}

Which is not a single json, but 3 differents jsons on a single file.

A correct json structure would have been:

[
{"key":"SEM-5765"}, 
{"key":"SEM-3325"}, 
{"key":"SEM-2755"},
]

Which is an array of json and a correct json structure.

So I think you have two possibilities :

you can correct the json structure
you can read your file by separating each line and traited each line as a single json

Either way, you will have to add a step to your code to loop through each line/json entity

answered Feb 05 '19 at 10:58

Dexter0015

1,029
9
14

Yes i'm aware of that but is there any way to correct JSON because API gives me this response which i agree partial JSON formatted? – Jitesh Sojitra Feb 05 '19 at 11:01
if you get the Json from an API you are stuck with this structure, but maybe you can separate the lines with the explode function you just need to find the correct char (maybe "\n" if each json entity is on it's own line). – Dexter0015 Feb 05 '19 at 11:11
JSON was wrong, but it worked fine by parsing lines starts with {key. – Jitesh Sojitra Feb 06 '19 at 03:44

score 1 · Answer 3 · answered Feb 05 '19 at 11:10

In your case you can use explode and implode php function to get your desire output. I have added few code lines :

$lineExplode = explode('}',$line);
$line = implode(',',$lineExplode);
$lineArray = json_decode("[".$line."]", true);

All other code is the same as your example:

<?php

echo "<table class='table create-release-note-table'>
                <thead>
                    <tr><th>#</th><th>Ticket ID</th><th>Status</th><th>Components</th><th>Affected Versions</th><th>Fix Versions</th><th>Description</th></tr>
                </thead>
            <tbody>";
    $i = 0;

    $resultFile = fopen($resultURL, "r");
    #$lines = file($resultURL, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    #print_r ($lines);
    #exit;

    while (!feof($resultFile)) {
        $line = trim(fgets ($resultFile));
        $line = str_replace("\\\"", "", $line);
        $line = stripslashes($line);
        $lineExplode = explode('}',$line);
        $line = implode(',',$lineExplode);
        $lineArray = json_decode("[".$line."]", true);
        echo "<tr><td>" . ++$i . "</td>";
        parseData($lineArray);
        echo "</tr>";
    }
    echo "</tbody></table>";
    fclose ($resultFile);


// Parse release note data

function parseData($array) {
   $value = str_replace(",", ";", $value);
   foreach ($array as $key => $value) {
     if (is_bool($value)) {
        echo ("<td>" . $value? 'true' : '') . "</td>";
     } else {
        echo "<td>" . $value . "</td>";
     }
  }
}

?>

Extract key value pair output from JSON file using PHP

JSON file content:

Expected:

Tried code:

3 Answers3

Linked