Question 405667 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: 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?

Tags:

2

Answers


  1. 0 
    WITH js AS (
    SELECT
        '{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}'::jsonb AS js_data
    )
SELECT
    jsonb_set(js_data, '{ST}', 
             jsonb_path_query_array(
                js_data, '($.ST[*])') || '["Pro", "SU"]'::jsonb)
FROM
    js
;

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

    This uses jsonb_path_query_array to pull the existing array value from the "ST" then concatenates it to the array being merged(jsonb_path_query_array). Then jsonb_set replaces the current array associated with the "ST" key with the new merged array created in jsonb_path_query_array. The CTE(WITH) is used to not have to repeat the entire jsonb object.

    Login or Signup to reply.
  2. 0

    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 || operator isn’t valid for JSON, so it will be necessary to cast JSON elements to JSONB.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search