使用Symfony QueryBuilder对JSON中的值求和

wfauudbj 于 2023-10-24 发布在其他

关注(0)|答案(1)|浏览(139)

我有一个问题，一个查询求和存储在一个JSON数组中的值。我有一个与Valid实体连接的Sheet实体。Valid有一个字段“$valid”对应于JSON数组。下面是一个JSON可能看起来像什么的例子。

{
  "bdc": {
    "service": {
       "operator": {
              "entries": [
                 {
                "priceDF": 100,
                "priceWT": 150,
                "firstNumber": 12,
                "paymentDate": {
                "date": "2023-08-07 00:00:00.000000",
                   "timezone": "Europe/Berlin",
                   "timezone_type": 3
                 },
                "secondNumber": 23,
                "paymentNumber": 1212,
                "dateFirstNumber": {
                   "date": "2023-08-07 00:00:00.000000",
                   "timezone": "Europe/Berlin",
                   "timezone_type": 3
                 }
                },
                {
                "priceDF": 100,
                "priceWT": 250,
                "firstNumber": 13,
                "paymentDate": {
                "date": "2023-08-07 00:00:00.000000",
                  "timezone": "Europe/Berlin",
                  "timezone_type": 3
                 },
                 "secondNumber": 24,
                 "paymentNumber": 1213,
                 "dateFirstNumber": {
                 "date": "2023-08-07 00:00:00.000000",
                    "timezone": "Europe/Berlin",
                    "timezone_type": 3
                  }
               }
            ]
      }
    },
    
       },
  "step": "Paiement",
  "type": "bdc",
  "anticipated": false
}

实际上，我想对存储在“entries”数组（路径：sheet.valid.bdc.service.operator.entries）中的对象中的值“priceDF”求和。
我试着写了这个查询：

$qb = $this->createQueryBuilder('s');
$qb->select("SUM(CAST(JSON_EXTRACT(v.dac, '$.bdc.service.operator.entries[*].priceWT') AS     DECIMAL(10,2)) ) as totalPriceWT")
->join('s.valid', 'v')
$query = $qb->getQuery();
$resultCP = $query->getResult();

但它总是返回0。我事实上我肯定写entries[*].priceWT不起作用，但我不知道怎么做。
有人知道如何在这种JSON中总结价值观吗？谢谢。

symfony

来源：https://stackoverflow.com/questions/76851757/sum-values-from-a-json-with-symfony-querybuilder

1条答案

按热度按时间

erhoui1w1#

感谢@ wchiandom，查询工作完美。我添加了其他条件，以满足我的项目要求，这是最终的查询（PHP）：

$sql = " SELECT
              SUM(sub.totalPriceWT) AS totalPrice,
              c.label
          FROM (
              SELECT
                  v.id,
                  (
                      SELECT SUM(priceWT)
                      FROM JSON_TABLE(
                          JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.bdc.service.operator.entries[*].priceWT')),
                          '$[*]' COLUMNS(priceWT DECIMAL(10, 2) PATH '$')
                      ) der
                  ) AS totalPriceWT
              FROM valid v
              JOIN sheet s ON v.sheet_id = s.id
              WHERE JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.type')) = 'bdc'
                  AND (
                      (YEAR(s.sent_date) = :currentYear AND CAST(JSON_EXTRACT(v.dac, '$.anticipated') AS SIGNED) = :anticipated2)
                      OR
                      (YEAR(s.sent_date) = :previousYear AND CAST(JSON_EXTRACT(v.dac, '$.anticipated') AS SIGNED) = :anticipated1)
                  )
                  AND s.perimeter_id = :perimeter
                  AND JSON_UNQUOTE(JSON_EXTRACT(v.dac, '$.bdc.service.operator.entries')) IS NOT NULL
          ) AS sub
          JOIN valid v2 ON sub.id = v2.id
          JOIN sheet s2 ON v2.sheet_id = s2.id
          JOIN cost_center c ON s2.cost_center_id = c.id
          GROUP BY c.id
          ";

  $conn = $this->manager->getConnection();
  $stmt = $conn->prepare($sql);
  $stmt->bindValue('perimeter', $perimeter->getId());
  $stmt->bindValue('currentYear', $year);
  $stmt->bindValue('previousYear', $year - 1);
  $stmt->bindValue('anticipated1', 1);
  $stmt->bindValue('anticipated2', 0);
  $resultSet = $stmt->executeQuery();
  $resultBDC = $resultSet->fetchAllAssociative();

赞(0）回复(0）举报 2023-10-24

我来回答

使用Symfony QueryBuilder对JSON中的值求和

1条答案

相关问题

热门标签

最新问答