Convert a CURL output of tabular data into a json array in PHP or via CLI command?-CodePudding

I'm trying to see what is the best method of converting a CURL output of tabular data as seen below into JSON.

I'm running:

curl -d "sql query here" -X POST https://xxx/database_query

Currently the output has | as the separator and I need to slice off the first line because its displaying the query string. I was thinking to pass this data through sed to clean it up a bit first?

> select ID, HEIGHT, COIN_BALANCE from account order by balance desc limit 20
ID                   | HEIGHT  | COIN_BALANCE
-7700097703553861699 | 1815934 | 299014795
-354651164223319404  | 1789433 | 150010434
514812057486191450   | 1815934 | 150000409
2819400133805325807  | 1789617 | 150000289
2045072049257519302  | 0       | 150000000
2825154884517497903  | 0       | 17904059

CodePudding user response：

Create an array by exploding of line breaks
Drop first row
Get next row with fieldnames, exploding by pipe and trim each
Loop over the rest and put it mapped with fieldnames into a new array, explode by pipe and trim each
Convert the results into JSON

$fromCurl = <<<'_EOT'
> select ID, HEIGHT, COIN_BALANCE from account order by balance desc limit 20
ID                   | HEIGHT  | COIN_BALANCE
-7700097703553861699 | 1815934 | 299014795
-354651164223319404  | 1789433 | 150010434
514812057486191450   | 1815934 | 150000409
2819400133805325807  | 1789617 | 150000289
2045072049257519302  | 0       | 150000000
2825154884517497903  | 0       | 17904059
_EOT;

$data = explode("\n", $fromCurl);
$firstLine = array_shift($data);
$fieldnames = array_map('trim', explode('|', array_shift($data)));

$result = [];
foreach($data as $line) {
    $values = array_map('trim', explode('|', $line));
    $result[] = array_combine($fieldnames, $values);
}
echo json_encode($result, JSON_PRETTY_PRINT);

prints

[
    {
        "ID": "-7700097703553861699",
        "HEIGHT": "1815934",
        "COIN_BALANCE": "299014795"
    },
    {
        "ID": "-354651164223319404",
        "HEIGHT": "1789433",
        "COIN_BALANCE": "150010434"
    },
    {
        "ID": "514812057486191450",
        "HEIGHT": "1815934",
        "COIN_BALANCE": "150000409"
    },
    {
        "ID": "2819400133805325807",
        "HEIGHT": "1789617",
        "COIN_BALANCE": "150000289"
    },
    {
        "ID": "2045072049257519302",
        "HEIGHT": "0",
        "COIN_BALANCE": "150000000"
    },
    {
        "ID": "2825154884517497903",
        "HEIGHT": "0",
        "COIN_BALANCE": "17904059"
    }
]