MySQL JSON_TABLE to PostgreSQL

Question

我刚接触过Postgres，刚刚将所有DBs从MySQL迁移到Postgres 12，所以我目前正在将MySQL文件中的所有MySQL.connectors代码从MySQL修改为Postgres。其中一个Python文件在MySQL中具有以下查询：

SELECT m.invoice_no, SUM(n.amount) AS patient_payable 
FROM ex_invoice m, JSON_TABLE(m.payment, '$[*]' COLUMNS(amount DOUBLE PATH '$.amount')) AS n 
GROUP BY m.invoice_no;

MySQL的输出：

invoice_no                          patient_payable
10008714b3dcc2486614275921d94db0    356.81
1000cdd124e28dae5829252384ecf792    0
1000fe2953204ccd49abaa6fd8053db2    235.4
100112a4bd354870d80f6e0177283a43    183.55
10012b1c2f8f36537bc5dc405a877116    114
1001966dcaf420ca0d3cd9f49e072bfa    149.8
1001f7bf52de5c8cfdfc1fab530d59cb    507
10025043f3e6a4a11525da9bae831f1c    456
1002f26f72ba1a8692abf929c1ab4825    221
1003ef6020adbefc3372c719d8abccca    150.68

支付列中的JSON格式如下所示：

  "[
    {
        ""_id"": ""088447"",
        ""ref"": """",
        ""corp"": """",
        ""void"": 0,
        ""amount"": 260,
        ""void_by"": """",
        ""void_on"": ""0000-00-00 00:00:00.000000"",
        ""location"": ""Queenstown"",
        ""invoice_id"": ""jNVJ6e131R310M4vG6cxiI526O8m4Fzu"",
        ""void_reason"": """"
    }
  ]"

我希望在Postgres中解释它，但是Postgres 12没有从JSON格式提取值的JSON_TABLE()和。我已经寻找了几天，并尝试了许多可能的解决方案，但未能成功，请帮助。

Answer 1

你很可能在找jsonb_array_elements()

SELECT m.invoice_no, 
       SUM((n.element ->> 'amount')::double precision) AS patient_payable 
FROM ex_invoice m
  cross join jsonb_array_elements(m.payment) as n(element) 
GROUP BY m.invoice_no;

这假设ex_invoice.payment被定义为jsonb (它应该是)。如果不是，则需要使用json_array_elements()。