MySQL JSON_TABLE to PostgreSQL-CodePudding

I am new to Postgres and just migrated all DBs from MySQL to Postgres 12. So I am currently modifying all of the MySQL.connectors code in the Python files from MySQL to Postgres. One of the Python files has this query in 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;

The output by 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

The JSON format in the payment column looks like this:

  "[
    {
        ""_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"": """"
    }
  ]"

I want it to be interpreted in Postgres but Postgres 12 doesn't have the JSON_TABLE() and COLUMNS() that extract the values from the JSON format. I have been searching for days and tried many possible solutions but couldn't succeed, please help.

CodePudding user response：

You are most probably looking for 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;

This assumes that ex_invoice.payment is defined as jsonb (which it should be). If it's not, you need to use json_array_elements() instead.