Zip array with values from JSON

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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)
              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


Method 2

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

    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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply