Zip array with values from JSON

Return jsonb object

If there are never additional keys in the JSON object, it can be as simple as:

SELECT jsonb '{"1": 15, "2": 15, "3": 15, "4": 15, "5": 15}' || (json->'key')
FROM   tbl;

Parentheses are required to overrule operator precedence!

This does not remove additional keys from the JSON object.
Returns NULL if the JSON object is not found. See example in the fiddle.

Consider notes for jsonb concatenation in the manual:

jsonb || jsonb jsonb

Concatenates two jsonb values. Concatenating two arrays generates an array containing all the elements of each input. Concatenating two objects generates an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases are treated by converting a non-array input into a single-element array, and then proceeding as for two arrays. Does not operate recursively: only the top-level array or object structure is merged.

Return int[]

Expanded the JSON object to a set with jsonb_each_text(), RIGHT JOIN to the full set (generated with generate_series() in a CTE), and aggregate with ARRAY constructor (fastest):

WITH a(key) AS (SELECT generate_series(1, 5)::text)
SELECT ARRAY (SELECT COALESCE(value::int, 15)
              FROM   jsonb_each_text(t.json->'key') RIGHT JOIN a USING (key)
              ORDER  BY key) AS int_arr
FROM   tbl t;

This trims any additional keys from the JSON object.
Returns an array of default values if the JSON object is not found. See example in the fiddle.

db<>fiddle here

Related:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Solved by user3684 (150,400 )
Apr 01, 2021 at 00:43