[SOLVED] When merging JSON objects with same keys, how do I append their values?

When merging JSON objects with same keys, how do I append their values?

I have two arrays as shown below, as i concat both the arrays - the ST key value in Array1 is replaced by values in ST key values in Array2,

Array 1:

{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}

Array 2:

{"ST": ["Pro", "SU"]}"

Expected output:

{"ST": ["Bin", "No", "Comp","Pro", "SU"], "OSS": ["Class"]}

How do I achieve this?

Solution

The following will perform the requested operation:

WITH demo AS (
    SELECT
      '{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}'::jsonb AS array1,
      '{"ST": ["Pro", "SU"]}'::jsonb AS array2
  )
SELECT
  array1 || JSONB_BUILD_OBJECT('ST', (array1 -> 'ST') || (array2 -> 'ST'))
FROM
  demo;

The expression works by creating a new ST element from the concatenated array values from the original ST elements and then takes advantage of the || operator's behavior of keeping the last value assigned to a common named element to create the final JSON. The || operator isn't valid for JSON, so it will be necessary to cast any JSON elements to JSONB.