CODEWITHSUNDEEP
perljsondbi
shaneburgess
shaneburgess

Reputation: 15882

How to loop json results in Perl

I am trying to output JSON from a perl script that accesses a mysql database.

How I can I loop through my query returns and turn that into JSON using the JSON module?

When I do this all I get is 1 return

while($query_handle->fetch()) {
    $jsonStructure->{event};
    $jsonStructure->{event}->{evid} = $evid;
    $jsonStructure->{event}->{component} = $component;
    $jsonStructure->{event}->{firstTime} = $firstTime;
    $jsonStructure->{event}->{lastTime} = $lastTime;
    $jsonStructure->{event}->{count} = $count;
    $jsonStructure->{event}->{summary} = $summary;
    $jsonStructure->{event}->{severity} = $severity;
}

Basically I have many events and don't know how to say event[0]...

Thank You

Upvotes: 0

Views: 1001

Answers (1)

hobbs
hobbs

Reputation: 239990

I think what you're looking for is this:

push @{ $jsonStructure->{events} }, {
    evid => $evid,
    component => $component,
    ...,
};

although even that is probably overkill, because you can probably do something like:

while (my $row = $dbh->fetchrow_hashref) {
  push @{ $jsonStructure->{events} }, $row;
}

if all of the column names in the DB are the same as the field names you want in the JSON, and you want all columns, or:

my @keys = qw(evid component firstTime ...);

while (my $row = $dbh->fetchrow_hashref) {
  my %hash;
  @hash{@keys} = @$row{@keys};
  push @{ $jsonStructure->{events} }, \%hash;
}

if you only want some columns, or:

# DB colname => JSON field name
my %mapping = (
  event_id => 'evid',
  component => 'component',
  first_time => 'firstTime',
  ...,
);

while (my $row = $dbh->fetchrow_hashref) {
  my %hash;
  @hash{ values %mapping } = @$row{ keys %mapping };
  push @{ $jsonStructure->{events} }, \%hash;
}

for a completely arbitrary mapping. Power of Perl and all that. :)

Upvotes: 3

Related Questions