Postgresql coalesce columns with JSON array

Question

Let's say that we have the following JSON object:

{"key_to_array":[{"key1":"value1","key2":{"var123":{"xyz":"I_need_version_1"}}},{"key1":"value1","key2":{"htg":{"uy1":"I_need_version_2"}}}]}}

inserted into a table:

drop table if exists modeling.modeling.pl_nested_json_array;
select '{"key_to_array":[{"key1":"value1","key2":{"var123":{"xyz":"I_need_version_1"}}},{"key1":"value1","key2":{"htg":{"uy1":"I_need_version_2"}}}]}' as json_data
into modeling.modeling.pl_nested_json_array
from modeling.modeling.some_other_table
limit 1;

In order to get to the I_need_version_* I must travel semi-constant way. 'Semi' means that some elements are varying (keys: var123, xyz, htg, uy1). There is a finite number of their combinations so I can handle them by writing different ways in my code such as:

json_array_elements(cast(json_data as json) -> 'key_to_array') -> 'key2' -> 'var123' ->> 'xyz' as col1

The problem is that my current approach:

/* many columns version - unwanted */
select 
    json_array_elements(cast(json_data as json) -> 'key_to_array') ->> 'key1' as key1,
    json_array_elements(cast(json_data as json) -> 'key_to_array') -> 'key2' -> 'var123' ->> 'xyz' as col1,
    json_array_elements(cast(json_data as json) -> 'key_to_array') -> 'key2' -> 'htg' ->> 'uy1' as col2
from modeling.modeling.pl_nested_json_array;

produces as many columns as there are ways to get to I_need_version_*. Since for each JSON_data only one way is valid (produces I_need_version_*) the rest of the columns col* are NULL, I decided to use COALESCE() in order to get one column col with the non-NULL value:

select 
    json_array_elements(cast(json_data as json) -> 'key_to_array') ->> 'key1' as key1,
    coalesce(
    json_array_elements(cast(json_data as json) -> 'key_to_array') -> 'key2' -> 'var123' ->> 'xyz',
    json_array_elements(cast(json_data as json) -> 'key_to_array') -> 'key2' -> 'htg' ->> 'uy1') as col
from modeling.modeling.pl_nested_json_array;

Upon executing this query I get ERROR: set-returning functions are not allowed in COALESCE.

The output I'm currently getting:

key1          col1               col2
value1        I_need_version_1   [NULL]
value1        [NULL]             I_need_version_2

What I'd like to get:

key1     col
value1   I_need_version_1
value1   I_need_version_2

It is [always] possible to nest the select as a derived query and coalesce the first output to the second: SELECT COALESCE(t.col1, t.col2) FROM (SELECT .. FROM table ..) t. — user2864740
– user2864740, Commented May 13, 2019 at 15:43
That said, the 'correct' approach is probably to ensure a scalar is supplied to coalesce.. — user2864740
– user2864740, Commented May 13, 2019 at 15:46
Thank you for the comment. I've tested your solution on my minimal reproducible example and it works. The problem (or rather inconvenience) is that the original table t from has some a lot of columns. If your solution is the only one feasible I'm willing to use but other that handle the problem directly are also welcome :) — balkon16
– balkon16, Commented May 13, 2019 at 15:52

Jeremy · Accepted Answer · 2019-05-14 00:56:49Z

1

What about just fully expanding the json and picking out the elements that you need? I'm not sure how many rows we're talking about, so performance could be an issue, but at least you wouldn't need to worry about the different paths.

WITH data as (
  SELECT '{"key_to_array":[{"key1":"value1","key2":{"var123":{"xyz":"I_need_version_1"}}},{"key1":"value1","key2":{"htg":{"uy1":"I_need_version_2"}}}]}'::jsonb as data
)
SELECT key1, k3_val
FROM data
CROSS JOIN jsonb_array_elements(data.data -> 'key_to_array') arr(elem)
CROSS JOIN jsonb_to_record(elem) as x(key1 text, key2 jsonb)
CROSS JOIN jsonb_each(key2) as k2(k2_key, k2_val)
CROSS JOIN jsonb_each_text(k2_val) as k3(k3_key, k3_val)
WHERE k3_val like 'I_need_version_%'
;
key1    k3_val
value1  I_need_version_1
value1  I_need_version_2

answered May 14, 2019 at 0:56

Jeremy

6,77322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql coalesce columns with JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related