How To Merge Multiple PostgreSQL JSON Arrays That Are The Result Of Another JSON Array Update On All Of Its Elements?

Question

Given the following two table columns jsonb type:

dividend_actual

{
  "dividends": [
    {
      "amount": "2.9800",
      "balanceDate": "2020-06-30T00:00:00Z"
    },
    {
      "amount": "4.3100",
      "balanceDate": "2019-06-30T00:00:00Z"
    }
  ],
  "lastUpdated": "2020-11-16T14:50:51.289649512Z",
  "providerUpdateDate": "2020-11-16T00:00:00Z"
}

dividend_forecast

{
  "dividends": [
    {
      "amount": "2.3035",
      "balanceDate": "2021-06-01T00:00:00Z"
    },
    {
      "amount": "3.0452",
      "balanceDate": "2022-06-01T00:00:00Z"
    },
    {
      "amount": "3.1845",
      "balanceDate": "2023-06-01T00:00:00Z"
    }
  ],
  "lastForecasted": "2020-11-13T00:00:00Z",
  "providerUpdateDate": "2020-11-16T00:00:00Z"
}

I would like to merge both dividends arrays from dividend_actual and dividend_forecast, but before merging them I want to add an extra field (forecast) on every single object.

I did try the following:

SELECT
    dividends
FROM
    stock_financial AS f
    INNER JOIN instrument AS i ON i.id = f.instrument_id,
    jsonb_array_elements(
        (f.dividend_forecast->'dividends' || jsonb '{"forecast": true}') || 
        (f.dividend_actual->'dividends' || jsonb '{"forecast": false}')
    ) AS dividends
WHERE
    i.symbol = 'ASX_CBA'
ORDER BY
    dividends ->>'balanceDate' DESC;

The above query gives me the following results:

{"forecast":true}
{"forecast":false}
{"amount":"3.1845","balanceDate":"2023-06-01T00:00:00Z"}
{"amount":"3.0452","balanceDate":"2022-06-01T00:00:00Z"}
{"amount":"2.3035","balanceDate":"2021-06-01T00:00:00Z"}
{"amount":"2.9800","balanceDate":"2020-06-30T00:00:00Z"}
{"amount":"4.3100","balanceDate":"2019-06-30T00:00:00Z"}

But what I need instead is the following output:

{"amount":"3.1845","balanceDate":"2023-06-01T00:00:00Z","forecast":true}
{"amount":"3.0452","balanceDate":"2022-06-01T00:00:00Z","forecast":true}
{"amount":"2.3035","balanceDate":"2021-06-01T00:00:00Z","forecast":true}
{"amount":"2.9800","balanceDate":"2020-06-30T00:00:00Z","forecast":false}
{"amount":"4.3100","balanceDate":"2019-06-30T00:00:00Z","forecast":false}

Could you provide some example input data. – Philipp Johannis Nov 19 '20 at 07:10 — Philipp Johannis, Nov 19 '20 at 07:10

Marco C · Accepted Answer · 2020-11-20T02:09:31.707

It turns out that it is not possible to update multiple jsons objects within a json array in a single operation by default.

To be able to do that a Postgres function needs to be created:

-- the params are the same as in aforementioned `jsonb_set`
CREATE OR REPLACE FUNCTION update_json_array_elements(target jsonb, path text[], new_value jsonb)
  RETURNS jsonb language sql AS $$
    -- aggregate the jsonb from parts created in LATERAL
    SELECT jsonb_agg(updated_jsonb)
    -- split the target array to individual objects...
    FROM jsonb_array_elements(target) individual_object,
    -- operate on each object and apply jsonb_set to it. The results are aggregated in SELECT
    LATERAL jsonb_set(individual_object, path, new_value) updated_jsonb
  $$;

The above function was suggested by kubak in this answer: https://stackoverflow.com/a/53712268/782390

Combined with this query:

SELECT
    dividends
FROM
    stock_financial AS f
    INNER JOIN instrument AS i ON i.id = f.instrument_id,
    jsonb_array_elements(
        update_json_array_elements(f.dividend_forecast->'dividends', '{forecast}', 'true') ||
        update_json_array_elements(f.dividend_actual->'dividends', '{forecast}', 'false')
    ) AS dividends
WHERE
    i.symbol = 'ASX_CBA'
ORDER BY
    dividends ->>'balanceDate' DESC;

I then get the following output, that it is exactly what I need:

{"amount":"3.1845","forecast":true,"balanceDate":"2023-06-01T00:00:00Z"}
{"amount":"3.0452","forecast":true,"balanceDate":"2022-06-01T00:00:00Z"}
{"amount":"2.3035","forecast":true,"balanceDate":"2021-06-01T00:00:00Z"}
{"amount":"2.9800","forecast":false,"balanceDate":"2020-06-30T00:00:00Z"}
{"amount":"4.3100","forecast":false,"balanceDate":"2019-06-30T00:00:00Z"}

How To Merge Multiple PostgreSQL JSON Arrays That Are The Result Of Another JSON Array Update On All Of Its Elements?

1 Answers1