converting JSON to CSV

cbtshare has asked for the wisdom of the Perl Monks concerning the following question:

Hello All,

I am try to convert aws metrics gotten from the awscli , and turn that into a more human readable format so users can better understand the data but I am not having much success, can you please assist.

my sample data

{
    "Datapoints": [
        {
            "Timestamp": "2017-03-01T04:05:00Z",
            "Average": 145.80000000000001,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T10:35:00Z",
            "Average": 182.19999999999999,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T05:30:00Z",
            "Average": 180.19999999999999,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T17:35:00Z",
            "Average": 140.40000000000001,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T01:05:00Z",
            "Average": 102.2,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T22:15:00Z",
            "Average": 114.2,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T05:45:00Z",
            "Average": 131.19999999999999,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T10:10:00Z",
            "Average": 201.40000000000001,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T09:00:00Z",
            "Average": 106.40000000000001,
            "Unit": "Count"
        },
        {
            "Timestamp": "2017-03-01T04:35:00Z",
            "Average": 159.59999999999999,
            "Unit": "Count"
        }
    ],
    "Label": "DatabaseConnections"
}
[download]

My code

#!/usr/bin/perl

use strict;
use warnings;
use JSON::PP;
use CGI;

my $json = JSON::PP->new->utf8;
#my $json_text = CGI->new->param('aws.json');
#my $perl_scalar = $json->decode( $json_text );

local $/;

open( my $fh, '<', 'aws.json' );
my $json_text = <$fh>;
my $perl_scalar = decode_json($json_text);

#print encode_json($perl_scalar);
print $json->utf8->encode($perl_scalar);
[download]

My results

{"Datapoints":[{"Average":145.8,"Timestamp":"2017-03-01T04:05:00Z","Un
+it":"Count"},{"Av
erage":182.2,"Timestamp":"2017-03-01T10:35:00Z","Unit":"Count"},{"Aver
+age":108,"Timesta
mp":"2017-03-01T18:50:00Z","Unit":"Count"},{"Average":134.8,"Timestamp
+":"2017-03-01T01:
45:00Z","Unit":"Count"},{"Average":99.4,"Timestamp":"2017-03-01T23:30:
+00Z","Unit":"Coun
t"},{"Average":234,"Timestamp":"2017-03-01T15:15:00Z","Unit":"Count"},
+{"Average":164.6,
"Timestamp":"2017-03-01T06:25:00Z","Unit":"Count"},{"Average":156.4,"T
+imestamp":"2017-0
3-01T12:55:00Z","Unit":"Count"},{"Average":111.2,"Timestamp":"2017-03-
+01T21:10:00Z","Un
it":"Count"},{"Average":156.8,"Timestamp":"2017-03-01T20:40:00Z","Unit
+":"Count"},{"Aver
age":163.2,"Timestamp":"2017-03-01T03:35:00Z","Unit":"Count"},{"Averag
+e":153.8,"Timesta
mp":"2017-03-01T11:50:00Z","Unit":"Count"},{"Average":155.8,"Timestamp
+":"2017-03-01T18:
20:00Z","Unit":"Count"},{"Average":109.8,"Timestamp":"2017-03-01T16:30
+:00Z","Unit":"Cou
nt"},{"Average":159.2,"Timestamp":"2017-03-01T08:15:00Z","Unit":"Count
+"},{"Average":145
.8,"Timestamp":"2017-03-01T23:00:00Z","Unit":"Count"}

...and more of the same till the end
[download]

Is there a better way to do it ? or am I missing something? Thank you

Comment on converting JSON to CSV Select or Download Code

Replies are listed 'Best First'.
Re: converting JSON to CSV by Anonymous Monk on Apr 06, 2017 at 22:42 UTC
Well what do you want the output to look like? Maybe this? `"Timestamp","Average","Unit" "2017-03-01T04:05:00Z","145.8","Count" "2017-03-01T10:35:00Z","182.2","Count" "2017-03-01T05:30:00Z","180.2","Count"` [download] Code... use warnings; use strict; use Text::CSV; use JSON::MaybeXS; my $json = <<'END_JSON'; { "Datapoints": [ { "Timestamp": "2017-03-01T04:05:00Z", "Average": 145.80000000000001, "Unit": "Count" }, { "Timestamp": "2017-03-01T10:35:00Z", "Average": 182.19999999999999, "Unit": "Count" }, { "Timestamp": "2017-03-01T05:30:00Z", "Average": 180.19999999999999, "Unit": "Count" } ], "Label": "DatabaseConnections" } END_JSON my $data = decode_json($json); my $csv = Text::CSV->new({auto_diag=>2, binary=>1, eol=>"\n", always_quote=>1 }); my @fields = qw/ Timestamp Average Unit /; $csv->print(select, \@fields); for my $datapoint ( @{ $data->{Datapoints} } ) { $csv->print(select, [ map {$datapoint->{$_}} @fields ]); } [download]	[reply] [d/l] [select]
Re^2: converting JSON to CSV by cbtshare (Monk) on Apr 07, 2017 at 01:50 UTC
I am sorry , I didnt see the code.My finished code is below, let me know if its good coding. `#!/usr/bin/perl use warnings; use strict; use Text::CSV; use JSON::MaybeXS; use FileHandle ; use IO::Handle; local( $/, *FH ) ; open( FH, '<', 'aws.json' ); my $text = <FH>; my $data = decode_json($text); my $csv = Text::CSV->new({auto_diag=>2,binary=>1, eol=>"\n", always_qu +ote=>1 }); my @fields = qw/ Timestamp Average Unit /; $csv->print(select, \@fields); for my $datapoint ( @{ $data->{Datapoints} } ) { $csv->print(select, [ map {$datapoint->{$_}} @fields ]); }` [download]	[reply] [d/l]
Re^3: converting JSON to CSV by AnomalousMonk (Archbishop) on Apr 07, 2017 at 03:25 UTC
... let me know if its good coding. What follows is in part my own personal preference, but a lot is current idiomatic Perl; I won't rigorously distingush between the two. My comments pertain to your `local( $/, FH ) ; open( FH, '<', 'aws.json' ); my $text = <FH>;` [download] section of code; the rest is from the `AnonyMonk`'s post and looks good to me. Caveat:* I haven't actually tested any of the code on which I'm commenting. `local( ..., FH ) ;` `open( FH, '<', 'aws.json' );` local-izing package global variables is better than nothing and you're already using the three-argument open, but it's even better to use a lexical filehandle and check* the success of the file `open`: `my $filename = 'aws.json';` `open my $fh, '<', $filename or die "opening '$filename': $!";` (You could also turn on autodie globally with a `use autodie;` statement, but I prefer the flexibility of individual die clauses.) `local( $/, ... ) ;` `...` `my $text = <FH>;` To do this idiomatic file "slurp" read, `$/` must be undefined. Again, globally undefining this variable (and that's effectively what you're doing, even using `local`) is not preferred: `local`-ize in the narrowest practicable scope: `my $text = do { local $/; <$fh>; };` With the previous two changes, the `local( $/, FH ) ;` statement (and its possibly problematic global effects) goes away entirely. (Update:* Actually, the `local( ..., FH ) ;` statement effectively has no semantic global effect WRT the `main::FH` glob because it is `local`-izing the glob at the top level of the script, where all globs come into existence anyway. The only practical "global" effect is on your expectations: you may be lulled into thinking that you have invoked some kind of magical protection for yourself when you have not.) Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re^3: converting JSON to CSV by shmem (Chancellor) on Apr 07, 2017 at 21:11 UTC
Just TIMTOWTDI... AnomalousMonk already mentioned the issue with local. Localizing is pointless when it happens at top level - in the same way as my variables are global variables if declared at top level in a program that consists in just 1 file without imports. So, local needs a place. A bare block is convenient: `my $text; # but see above ;-) { local( $/, *FH ) ; open( FH, '<', 'aws.json' ); $text = <FH>; } # $/ restored to default here # FH is an empty typeglob in the symbol table` [download] See also my/local, space/time (was: Re: The difference between my and local) perl -le'print map{pack c,($-++?1:13)+ord}split//,ESEL'	[reply] [d/l]
Re^4: converting JSON to CSV by haukex (Archbishop) on Apr 08, 2017 at 09:14 UTC
Re^2: converting JSON to CSV by cbtshare (Monk) on Apr 07, 2017 at 00:10 UTC
yes, that looks much better, how is that done?	[reply]