所有记录(包括空数组)的Json_table

Question

我有像这个[{ amount: 5 }, { amount: 3.4} ]这样的数组的列，有些记录是空的[]

我在其他列中使用sum。当我尝试在JSON_TABLE中使用sum时，它只给出json表不为空的记录的结果。

select count(1), cargo.invoice_currency_code, sum(t.amount) from cargo, JSON_TABLE(cargo.invoice_extra_charges, '$[*]' COLUMNS (amount FLOAT PATH '$.amount')) as t group by cargo.invoice_currency_code;

我能做什么?

Answer 1

使用JSON_TABLE()是派生表的横向内部连接，即使使用逗号样式的联接语法也是如此。

但是，如果您的JSON文档中的数组为空，它将生成零行，因此没有什么可连接的。内部联接操作消除了cargo表中相应的行。

但是，如果显式使用外部联接，则可以覆盖隐式内部联接：

mysql> select count(1), cargo.invoice_currency_code, sum(t.amount) 
 from cargo left outer join JSON_TABLE(cargo.invoice_extra_charges, 
   '$[*]' COLUMNS (amount FLOAT PATH '$.amount')
  ) as t on true 
 group by cargo.invoice_currency_code;

+----------+-----------------------+-------------------+
| count(1) | invoice_currency_code | sum(t.amount)     |
+----------+-----------------------+-------------------+
|        2 |                     1 | 8.400000095367432 |
|        1 |                     2 |              NULL |
+----------+-----------------------+-------------------+

和作为NULL返回，因为它没有求和值，但是可以使用COALESCE()函数将其默认为零

mysql> select count(1), cargo.invoice_currency_code, 
  COALESCE(sum(t.amount), 0) AS sum 
 from cargo left outer join JSON_TABLE(cargo.invoice_extra_charges, 
   '$[*]' COLUMNS (amount FLOAT PATH '$.amount')
  ) as t on true 
 group by cargo.invoice_currency_code;

+----------+-----------------------+-------------------+
| count(1) | invoice_currency_code | sum               |
+----------+-----------------------+-------------------+
|        2 |                     1 | 8.400000095367432 |
|        1 |                     2 |                 0 |
+----------+-----------------------+-------------------+