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:
<code>{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}
</code>
<code>{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}
</code>
{"ST": ["Bin", "No", "Comp"], "OSS": ["Class"]}
Array 2:
<code>{"ST": ["Pro", "SU"]}"
</code>
<code>{"ST": ["Pro", "SU"]}"
</code>
{"ST": ["Pro", "SU"]}"
Expected output:
<code>{"ST": ["Bin", "No", "Comp","Pro", "SU"], "OSS": ["Class"]}
</code>
<code>{"ST": ["Bin", "No", "Comp","Pro", "SU"], "OSS": ["Class"]}
</code>
{"ST": ["Bin", "No", "Comp","Pro", "SU"], "OSS": ["Class"]}
How do I achieve this?
3
The following will perform the requested operation:
<code>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;
</code>
<code>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;
</code>
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.
<code>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"]}
</code>
<code>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"]}
</code>
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.