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

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...
    <Reveal this spoiler or all in this thread>
[reply]
[d/l]
[select]

      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]
        ... 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]

        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]
      yes, that looks much better, how is that done?
[reply]

Back to Seekers of Perl Wisdom