SQL COUNT / SUM JSON_ARRAY-CodePudding

I unnested a json file and want to calculate based on the columns (like count(categorie)):

SELECT
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'booking_date' AS Date,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'amount' AS Amount,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'currency' AS Currency,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'counter_holder' AS Counter_holder,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'counter_iban' AS iban,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'category_id' AS Categorie
FROM loan_applications AS l
LEFT JOIN risk_scores AS p1
  ON l.id = p1.loan_application_id
where human_readable_id = 'XXX';

How can I make calculations within the columns?

CodePudding user response：

First you can move the json_array_elements() function from the SELECT clause to the FROM clause by adding a JOIN LATERAL :

SELECT
       l->'booking_date' AS Date,
       l->'amount' AS Amount,
       l->'currency' AS Currency,
       l->'counter_holder' AS Counter_holder,
       l->'counter_iban' AS iban,
       l->'category_id' AS Categorie
FROM loan_applications AS l
LEFT JOIN risk_scores AS p1
  ON l.id = p1.loan_application_id
CROSS JOIN LATERAL json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers') AS l
where human_readable_id = 'XXX';

Then you can add any kind of calculation in the SELECT clause associated with a GROUP BY clause when using aggregate functions : Count(*), ...

CodePudding user response：

You can build your calculation based on a subquery, for instance:

SELECT count,sum,... FROM (
SELECT
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'booking_date' AS Date,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'amount' AS Amount,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'currency' AS Currency,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'counter_holder' AS Counter_holder,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'counter_iban' AS iban,
       json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->'category_id' AS Categorie
FROM loan_applications AS l
LEFT JOIN risk_scores AS p1
  ON l.id = p1.loan_application_id
where human_readable_id = 'XXX') as subquery;

or can build a CTE