Zip array with values from JSON

Question

Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {"1": 0, "2": 3, "3": 15, "4": 15, "5": 0}. The 15 would be like a default value I pull from another column.

Another acceptable output would be an array with the values '{0, 3, 15, 15, 0}'.

Sounds too complex for pure SQL? If not possible, help with a PL/pgSQL would also be appreciated.

• Erwin Brandstetter: Does that input array just tell us the desired length of the output array, so all we actually need to know is: 5. Or does it provide a list of indexes that can differ from a plain count?
• Ivan: It's a fixed input, always 1..5

Answer 1

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?

Answer 2

It's amazing how posting a question helps the brain find solutions. Just occurred to me I don't need to get fancy:

SELECT
 ARRAY[
 COALESCE((json->'key'->'1')::text, other_col::text, '0')::int,
 COALESCE((json->'key'->'2')::text, other_col::text, '0')::int,
 COALESCE((json->'key'->'3')::text, other_col::text, '0')::int,
 COALESCE((json->'key'->'4')::text, other_col::text, '0')::int,
 COALESCE((json->'key'->'5')::text, other_col::text, '0')::int
 ]
...