How to extract data from this nested array

Question

I'm trying to extract data from a JSON string generated by my PHP web application.

What I've tried:

sub SqlQuery {

    use strict;
    use warnings;

    $SqlQuery = shift;

    my $MYSQLDB     = "db";
    my $SQLuser     = "user";
    my $SQLpassword = "password";

    my $dsn = "DBI:mysql:database = $MYSQLDB;host=localhost;port=3306";
    my $dbh = DBI->connect($dsn, $SQLuser, $SQLpassword);

    my $DrugsJson = '';
    my @myjson;

    say "Query is " . $SqlQuery;

    my $rows = $dbh->selectall_arrayref(
        $SqlQuery, { Slice => {} }
    );

    my $ct = 0;

    foreach my $row ( @$rows ) {

        say "Count: $ct";

        $DrugsJson = $row->{medicine};
        say $DrugsJson;

        @myjson = decode_json($DrugsJson);
        say Dumper @myjson;

        foreach my $row (@myjson) {

            say "Row is " .ref $row;

            foreach my $element ( @$row ) {

                say "Element is " .ref $element;

                foreach my $subelement (@{$element}) {
                    say "SubElement is " . $subelement;
                }
            }
        }

        foreach ($myjson[0][0]) {
            push @Brand, $_;
        }

        say "Listing brands..";
        say Dumper @Brand;
    }
}

I'm trying to read the data containing "NASOCLEAR NASAL SPRAY","ASCORIL FLU SYRUP","CALPOL 120MG SUSPENSION" into array @Brand, "SODIUM CHLORIDE 0.65%W/V","CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG","PARACETAMOL 120MG/5ML" into @Generic and so on.

Sample output:

Query is SELECT `diagnosis`, `ICDCode`, `ICDVer`, `history`, `examination`, `medicine`, `investigation`, `consultations` FROM `clinical` WHERE `CheckinNo`=85 LIMIT 1
Count: 0
[["NASOCLEAR NASAL SPRAY","ASCORIL FLU SYRUP","CALPOL 120MG SUSPENSION"],["SODIUM CHLORIDE 0.65%W/V","CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG","PARACETAMOL 120MG/5ML"],["1","3","5"],["Drops","mL","mL"],["TDS","BD","TDS"],["5","5","3"],["days","days","days"],"85"]
$VAR1 = [
          [
            'NASOCLEAR NASAL SPRAY',
            'ASCORIL FLU SYRUP',
            'CALPOL 120MG SUSPENSION'
          ],
          [
            'SODIUM CHLORIDE 0.65%W/V',
            'CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG',
            'PARACETAMOL 120MG/5ML'
          ],
          [
            '1',
            '3',
            '5'
          ],
          [
            'Drops',
            'mL',
            'mL'
          ],
          [
            'TDS',
            'BD',
            'TDS'
          ],
          [
            '5',
            '5',
            '3'
          ],
          [
            'days',
            'days',
            'days'
          ],
          '85'
        ];

Row is ARRAY
Element is ARRAY
SubElement is NASOCLEAR NASAL SPRAY
SubElement is ASCORIL FLU SYRUP
SubElement is CALPOL 120MG SUSPENSION
Element is ARRAY
SubElement is SODIUM CHLORIDE 0.65%W/V
SubElement is CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG
SubElement is PARACETAMOL 120MG/5ML
Element is ARRAY
SubElement is 1
SubElement is 3
SubElement is 5
Element is ARRAY
SubElement is Drops
SubElement is mL
SubElement is mL
Element is ARRAY
SubElement is TDS
SubElement is BD
SubElement is TDS
Element is ARRAY
SubElement is 5
SubElement is 5
SubElement is 3
Element is ARRAY
SubElement is days
SubElement is days
SubElement is days
Element is 
Can't use string ("85") as an ARRAY ref while "strict refs" in use at ./sendpresc.pl line 121.

Answer 1

The JSON you are getting, after calling decode_json, is turned in Perl into an array ref of array refs for all rows but the last one (or following the suggestion in a comment "a reference to an array of array references"), that contains a scalar (the row id). To traverse the JSON you may do:

use strict;
use warnings;

use JSON;

my $DrugsJson = '[["NASOCLEAR NASAL SPRAY","ASCORIL FLU SYRUP","CALPOL 120MG SUSPENSION"],["SODIUM CHLORIDE 0.65%W/V","CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG","PARACETAMOL 120MG/5ML"],["1","3","5"],["Drops","mL","mL"],["TDS","BD","TDS"],["5","5","3"],["days","days","days"],"85"]';

my $myjson = decode_json($DrugsJson);

# Traverse the JSON
foreach my $i ( @$myjson ) {
  print "Row is ", ref $i, "\n";
  if( ref $i eq 'ARRAY' ) {
    foreach my $j ( @$i ) {
      print "Sub-element is ", $j, "\n";
    }
  }
  else {
    print "Element is ", $i, "\n";
  }
}

# Create arrays for Brand and Generic
my @Brand   = @{ $myjson->[0] };
my @Generic = @{ $myjson->[1] };

print 'Brand has: ', join(', ', @Brand), "\n";
print 'Generic has: ', join(', ', @Generic), "\n";

I have created the bit of code to have a JSON turned into a Perl data structure (I don't have your database.) It is better to receive the JSON in a scalar variable like $myjson = decode_json($DrugsJson). From there, ref will tell you whether what you have is a ref (a string with "ARRAY", "HASH" and so on) or a scalar (no value).

In order to address the element $i, $j of an array ref of array refs, you may use $myjson->[$i][$j] which is the compact form of $myjson->[$i]->[$j]. You may also turn an array ref into an array using the conversion @{ $array_ref }. For simple expressions, you may also use @$array_ref. But for an element of an array ref, you need the longer @{ $myjson->[0] }.

The output:

Row is ARRAY
Sub-element is NASOCLEAR NASAL SPRAY
Sub-element is ASCORIL FLU SYRUP
Sub-element is CALPOL 120MG SUSPENSION
Row is ARRAY
Sub-element is SODIUM CHLORIDE 0.65%W/V
Sub-element is CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG
Sub-element is PARACETAMOL 120MG/5ML
Row is ARRAY
Sub-element is 1
Sub-element is 3
Sub-element is 5
Row is ARRAY
Sub-element is Drops
Sub-element is mL
Sub-element is mL
Row is ARRAY
Sub-element is TDS
Sub-element is BD
Sub-element is TDS
Row is ARRAY
Sub-element is 5
Sub-element is 5
Sub-element is 3
Row is ARRAY
Sub-element is days
Sub-element is days
Sub-element is days
Row is
Element is 85
Brand has:   NASOCLEAR NASAL SPRAY, ASCORIL FLU SYRUP, CALPOL 120MG SUSPENSION
Generic has: SODIUM CHLORIDE 0.65%W/V, CHLORPHENIRAMINE 2MG+PHENYLEPHRINE 5MG, PARACETAMOL 120MG/5ML