1

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.

2
  • 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? Mar 30 at 0:04
  • It's a fixed input, always 1..5
    – Ivan
    Mar 31 at 21:13
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:

0

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
  ]
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.