如何在JSON数组中创建JSON数组

德瓦什·阿格劳瓦尔（Devesh Agrawal）

MYSQL中的3个表

table_product - product_id, product_name
table_variane - variant_id, variant_name
table_product_variants - product_id,  variant_id, MRP, SellPrice

我想以以下格式从所有产品的Perl中创建JSON数据：

[
    {
        "ProductID": "1",
        "ProductName": "Green Detergent Bar",
        "Variants": [
            {
                "VariantID": "1",
                "VariantName": "500GM",
                "MRP": "20.00",
                "SellPrice": "19.50"
            },
            {
                "VariantID": "2",
                "VariantName": "1KG",
                "MRP": "40.00",
                "SellPrice": "38.00"
            }
        ]
    },
    {
        "ProductID": "2",
        "ProductName": "ABCD",
        "Variants": [
            {
                "VariantID": "3",
                "VariantName": "1KG",
                "MRP": "200.00",
                "SellPrice": "190.50"
            },
            {
                "VariantID": "2",
                "VariantName": "1KG",
                "MRP": "40.00",
                "SellPrice": "38.00"
            }
        ]
    }
]

这是Perl代码

my $sql_query = "";     //need to fill this.
my $statement = $db_handle->prepare ($sql_query) or die "Couldn't prepare query '$sql_query': $DBI::errstr\n";  
$statement->execute() or die "SQL Error: $DBI::errstr\n";  

my @loop_data = ();

while (my @data = $statement->fetchrow_array())
{
    my %data =  //need to fill this too.

    push(@loop_data, \%data);
}

my $json_text = to_json(\@loop_data);
print $json_text;

请帮助填写SQL查询和while循环。

它只是一个蓝图。代码中的任何修改也可以。

池上

您需要数组（而不是散列）会使事情变得有些复杂。

选项1

使用两个查询，一个查询找到产品，另一个查询找到产品的变体。

my $product_sth = $dbh->prepare("
   SELECT product_id,
          product_name
     FROM table_product
");

my $variant_sth = $dbh->prepare("
   SELECT tv.variant_id,
          tv.variant_name,
          tvp.MRP,
          tvp.SellPrice
     FROM table_product_variants AS tpv
     JOIN table_variant AS tv
       ON tpv.variant_id = tv.variant_id
    WHERE tpv.product_id = ?
");

my @data;
while (my $product_row = $product_sth->fetchrow_hashref()) {
   my @variants;
   $variant_sth->execute($product_row->{product_id});
   while (my $variant_row = $variant_sth->fetchrow_hashref()) {
      push @variants, {
         VariantID   => $variant_row->{variant_id},
         VariantName => $variant_row->{variant_name},
         MRP         => $variant_row->{MRP},
         SellPrice   => $variant_row->{SellPrice},
      };
   }

   push @data, {
      ProductID   => $product_row->{product_id},
      ProductName => $product_row->{product_name},
      Variants    => \@variants,
   };
}

my $data_json = to_json(\@data);

选项2

使用单个查询时，可以使用HoA对产品的变体进行分组。

my $sth = $dbh->prepare("
   SELECT tp.product_id,
          tp.product_name,
          tv.variant_id,
          tv.variant_name,
          tvp.MRP,
          tvp.SellPrice
     FROM table_product AS tp
     JOIN table_product_variants AS tpv
       ON tp.product_id = tpv.product_id
     JOIN table_variant AS tv
       ON tpv.variant_id = tv.variant_id
");

my %data;
while (my $row = $sth->fetchrow_hashref()) {
   my $product_id = $row->{product_id};

   my $product = $data{$product_id} ||= {
      ProductID   => $row->{product_id},
      ProductName => $row->{product_name},
      Variants    => [],
   };

   push @{ $product->{Variants} }, {
      VariantID   => $row->{variant_id},
      VariantName => $row->{variant_name},
      MRP         => $row->{MRP},
      SellPrice   => $row->{SellPrice},
   };
}

my $data_json = to_json([ values(%data) ]);

选项3

使用单个查询时，可以使用排序对产品的变体进行分组。

my $sth = $dbh->prepare("
   SELECT tp.product_id,
          tp.product_name,
          tv.variant_id,
          tv.variant_name,
          tvp.MRP,
          tvp.SellPrice
     FROM table_product AS tp
     JOIN table_product_variants AS tpv
       ON tp.product_id = tpv.product_id
     JOIN table_variant AS tv
       ON tpv.variant_id = tv.variant_id
    ORDER BY tp.product_id
");

my $last_product_id = 0;
my @data;
while (my $row = $sth->fetchrow_hashref()) {
   my $product_id = $row->{product_id};

   if ($product_id != $last_product_id) {
      $last_product_id = $product_id;
      push @data, {
         ProductID   => $row->{product_id},
         ProductName => $row->{product_name},
         Variants    => [],
      };
   }

   push @{ $data[-1]{Variants} }, {
      VariantID   => $row->{variant_id},
      VariantName => $row->{variant_name},
      MRP         => $row->{MRP},
      SellPrice   => $row->{SellPrice},
   };
}

my $data_json = to_json(\@data);

与选项2相比，这是一项额外的工作，但是它具有最小的客户端内存占用空间（如果您不必将所有内容都保留在内存中）。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。